Zahl aus Text rausholen!!

Bild

Betrifft: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 09.03.2005 15:29:35
Hallo,
wie kann ich aus folgedem Satz die Nummer rausholen?
2. Erie Manufacturing Co (006085906) - UNITED STATES
Danke
Matthias

Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: c0bRa
Geschrieben am: 09.03.2005 15:36:55
hallo...
wenn die immer in den klammern sind, dann:
=RECHTS(LINKS(A1;FINDEN(")";A1;1)-1);LÄNGE(LINKS(A1;FINDEN(")";A1;1)-1))-FINDEN("(";LINKS(A1;FINDEN(")";A1;1)-1);1))

Das Forum lebt auch von Rückmeldungen...
c0bRa
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: c0bRa
Geschrieben am: 09.03.2005 15:41:54
kürzer:
=TEIL(A1;1+FINDEN("(";A1;1);FINDEN(")";A1;1)-FINDEN("(";A1;1)-1)
c0bRa
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 09.03.2005 15:42:20
Hi,
danke schon mal!
Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130782) - SINGAPORE nimmt er die Klammer mit dem S
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: c0bRa
Geschrieben am: 09.03.2005 15:45:05
ja wenn 2 klammern drin sind wirds haarig...
glaub da wird dir nix übrig bleiben als die zahl manuell rauszuholen, weil jetzt noch abzufragen, wieviel klammern drin sind und dann die formel noch anpassen daran, ist ein ding der unmöglichkeit... (zumindest für mich)
c0bRa
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 09.03.2005 15:46:09
Ich bastele auch schon die ganze Zeit rum!
Trotzdem Danke
Matthias
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: c0bRa
Geschrieben am: 09.03.2005 15:56:39
andere möglichkeit:
https://www.herber.de/bbs/user/19356.txt
schau dir mal die funktionen an...
damit wäre deine zahl
=gettok(gettok(A1;"(";numtok(A1;"("));")";1)
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 09.03.2005 15:59:48
Ich werde es ma probieren....
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: c0bRa
Geschrieben am: 09.03.2005 16:12:31
zur erklärung:
gettok(text;token;nummer) sucht die anzahl der tokens (in dem fall "(" bzw ")") und trennt anhand der tokens dann ab, anhand der nummer (3. kriterium) wird das entsprechende token zurückgegeben. bei der kombination gettok(...;...;numtok(...)) gibt er also das letzte token zurück.
numtok(text;token) gibt die anzahl der entsprechenden tokens wieder.
sind noch ne reihe weiterer funktionen drin.
kannst auch noch die funktion lasttok dazubasteln:
Public

Function lasttok(text As String, token As String)
    lasttok = gettok(text, token, numtok(text, token))
End Function

wäre schön zu hören, obs geklappt hat.

Das Forum lebt auch von Rückmeldungen...
c0bRa
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: FP
Geschrieben am: 09.03.2005 20:58:01
Hallo Matthias,
Tabelle1
 ABC
1Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130789) - SINGAPORE595130789) - SINGAPORE595130789
Formeln der Tabelle
B1 : =TEIL(A1;SUCHEN("#";WECHSELN(A1;"(";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"(";""))))+1;99)
C1 : =LINKS(B1;SUCHEN(")";B1)-1)
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Servus aus dem Salzkammergut
Franz
Bild

Betrifft: AW: such's Dir aus...
von: FP
Geschrieben am: 09.03.2005 21:36:57
Hallo Matthias,
Zeile 1: Formel ohne Hilfsspalte
Zeile 2: Formel mit Hilfsspalte
Zeile 3: per UDF (VBA)
Zeile 4 zeigt eine weitere Verwendungsmöglichkeit der UDF
Tabelle1
 ABC
1Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130789) - SINGAPORE595130789 
2Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130789) - SINGAPORE595130789595130789) - SINGAPORE
3Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130789) - SINGAPORE595130789 
4Aber hier 19. Invensys Software Systems (123,456) Pte Ltd (595130789) - SINGAPORE123,456595130789
Formeln der Tabelle
B1 : =LINKS(TEIL(A1;SUCHEN("#";WECHSELN(A1;"(";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"(";""))))+1;99);SUCHEN(")";TEIL(A1;SUCHEN("#";WECHSELN(A1;"(";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"(";""))))+1;99))-1)+0
B2 : =LINKS(C2;SUCHEN(")";C2)-1)+0
C2 : =TEIL(A2;SUCHEN("#";WECHSELN(A2;"(";"#";LÄNGE(A2)-LÄNGE(WECHSELN(A2;"(";""))))+1;99)
B3 : =GETZAHL(A3;"(";")")
B4 : =GETZAHL($A4;"(";")";SPALTE(A4))
C4 : =GETZAHL($A4;"(";")";SPALTE(B4))
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

hier die UDF:

Function GetZahl(rng As Range, str1 As String, str2 As String, _
      Optional bSNr As Byte = 255) As Double
  Dim bL    As Byte   'Länge des Textes in rng
  Dim bA    As Byte   'Anzahl der str1 Zeichen in rng
  Dim strT  As String 'Hilfszeichenkette
  bL = Len(rng)
  bA = bL - Len(Application.Substitute(rng, str1, ""))
  If bA < bSNr Then bSNr = bA
  strT = Application.Substitute(rng, str2, "#", bSNr)
  strT = Left(strT, InStr(strT, "#") - 1)
  For bL = 1 To bSNr
    strT = Mid(strT, InStr(strT, str1) + 1, 99)
  Next
  GetZahl = CDbl(strT)
End Function

Servus aus dem Salzkammergut
Franz
Bild

Betrifft: AW: kleine Spielerei
von: FP
Geschrieben am: 09.03.2005 22:06:52
Hallo Matthias,
hier das Ergebnis:
Tabelle1
 AB
3Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130789) - SINGAPORE595130789
4Aber hier 19. Invensys Software Systems (123,456) Pte Ltd (595130789) - SINGAPORE123,456
5Aber hier 19. Invensys Software Systems (123,456) Pte Ltd (595130789) - SINGAPORE595130789
6Aber hier 19. Invensys Software Systems (123,456) Pte Ltd (595130789) - SINGAPORE595130912,456
7Aber hier (19.) Invensys Software Systems (123,456) Pte Ltd (595130789) - SINGAPORE595130931,456
Formeln der Tabelle
B3 : =GETZAHL(A3;"(";")")
B4 : =GETZAHL($A4;"(";")";ZEILE(A1))
B5 : =GETZAHL($A5;"(";")";ZEILE(A2))
B6 : =GETZAHL($A6;"(";")";;"S")
B7 : =GETZAHL($A7;"(";")";;"S")
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

' *----------------------------------------------------------------------------*
' * GetZahl: Zahl aus Text extrahieren unter Angabe von Begrenzungszeichen *
' * Parameter: rng - Bereich der den Text enthält
' * str1 - letztes Zeichen vor der Zahl
' * str2 - erstes Zeichen nach der Zahl
' * Opionen: bZNr - die wievielte Zahl soll ermittelt werden ?
' * keine Angabe = die letzte
' * opt - "S" = die Summe aller Zahlen zwischen den Begrenzungen
' * "Z" = die x. Zahl ( Standard )
' *----------------------------------------------------------------------------*

Function GetZahl(rng As Range, str1 As String, str2 As String, _
      Optional bZNr As Byte = 255, _
      Optional opt As String = "Z") As Double
  Dim bL    As Byte   'Länge des Textes in rng
  Dim bA    As Byte   'Anzahl der str1 Zeichen in rng
  Dim strT  As String 'Hilfszeichenkette
  strT = rng
  bL = Len(strT)
  bA = bL - Len(Application.Substitute(strT, str1, ""))
  If bA < bZNr Or opt = "S" Then bZNr = bA
  For bZNr = IIf(opt = "Z", bZNr, 1) To bZNr
    If strT <> rng Then strT = rng
    strT = Application.Substitute(strT, str2, "#", bZNr)
    strT = Application.Substitute(Left(strT, InStr(strT, "#") - 1), _
      str1, "#", bZNr)
    strT = Mid(strT, InStr(strT, "#") + 1, 99)
    GetZahl = GetZahl + CDbl(strT)
  Next
End Function

Servus aus dem Salzkammergut
Franz
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Peter W
Geschrieben am: 09.03.2005 15:37:45
Servus,
http://www.excelformeln.de/formeln.html?welcher=102
MfG
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Peter Feustel
Geschrieben am: 09.03.2005 15:43:50
Hallo Matthias,
angenommen deine Werte stehen in Spalte A ab Zeile 2
dann liefert folgendes Makro dir die Werte in Spalte B ab zeile 2.


Sub Extrahieren()
Dim lLetzte  As Long             ' letzte verwendete Zeile in Spalte A
Dim lZeile   As Long             ' For/Next Lauf-Index
Dim iPos     As Integer          ' Position der Klammer auf
Dim iWoPos   As Integer          ' For/Next Lauf-Index
Dim sWoZei   As String * 1       ' Zeichen im zu separierenden Wort
   lLetzte = IIf(Range("A65536") <> "", 65536, Range("A65536").End(xlUp).Row)
   
   For lZeile = 2 To lLetzte     ' ab Zeile 2 bis Ende Daten
      iPos = InStr(Range("A" & lZeile).Value, "(") ' Position Klammer auf suchen
      If iPos > 0 Then                             ' Klammer auf vorhanden ?
         Range("B" & lZeile).ClearContents         ' Ergebnis-Zelle leeren
'    Position Klammer auf hochrechnen bis kein Leerzeichen mehr folgt
'    so können dem Klammer auf kein, bis beliebig viele Leerzeichen folgen
         Do
            iPos = iPos + 1
         Loop Until Mid(Range("A" & lZeile).Value, iPos, 1) <> " "
'    die Zeichen ab Position Kunden-Nr einzeln übernehmen und an die neue
'    Zelle übergeben, bis Klammer zu gefunden wird.
         For iWoPos = iPos To Len(Range("A" & lZeile).Value)
            sWoZei = Mid(Range("A" & lZeile).Value, iWoPos, 1)
            If sWoZei <> ")" Then ' auf Klammer zu verproben
               Range("B" & lZeile).Value = Range("B" & lZeile).Value & sWoZei
             Else
               Exit For           ' Leerzeichen gefunden => For/Next verlassen
            End If
         Next iWoPos              ' nächste Wort-Position
      End If
   Next lZeile                    ' nächste Zeile bearbeiten
 
End Sub

     Code eingefügt mit Syntaxhighlighter 2.5

Viele Grüße Peter
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 09.03.2005 15:49:54
Hi Peter,
super Makro!!!
Aber hier 19. Invensys Software Systems (S) Pte Ltd (595130782) - SINGAPORE
nimmt er die erste Klammer!
Wie kann man das Makro abändern?
Matthias
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: bst
Geschrieben am: 09.03.2005 16:06:57
Auch Hallo,
falls es denn ein bischen 'regexp' sein darf ;-)
Die {3,} bedeutet mindestens 3 Ziffern.
cu, Bernd
--
Option Explicit

Sub SplitMe(src As Range, pattern As String, Optional ic As Boolean = False)
   Dim cell As Range
   Dim i As Integer
   Dim re As Object, m As Object
   
   Set re = CreateObject("vbscript.regexp")
   re.pattern = pattern
   re.ignorecase = ic
   For Each cell In src
      Set m = re.Execute(cell.Value)
      If m.Count > 0 Then
         For i = 0 To m(0).submatches.Count - 1
            cell.Offset(0, i + 1).Value = CStr(m(0).submatches(i))
         Next i
      End If
   Next
   Set m = Nothing
   Set re = Nothing
End Sub


Sub Test()
   SplitMe Range("A1:A10"), "\((\d{3,})\)"
End Sub

Bild

Betrifft: AW: Zahl aus Text rausholen!! -Formel
von: Martin Beck
Geschrieben am: 09.03.2005 16:39:53
Hallo Matthias,
wenn genau 2 Klammerpaare vorkommen dann z.B.
=TEIL(A1;FINDEN("(";A1;FINDEN("(";A1)+1)+1;FINDEN(")";A1;FINDEN(")";A1)+1)-FINDEN("(";A1;FINDEN("(";A1)+1)-1)
Die Formel von der Site Excelformeln.de funktioniert dann, wenn am Anfang des Strings nicht noch eine Zahl steht. Du müßtest also in Deinem Beispiel zuerst die "19" entfernen.
Wenn es nicht zuviele sind, poste doch mal sämtliche Texte.
Grüße
Martin Beck
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Peter Feustel
Geschrieben am: 09.03.2005 17:08:52
Hallo Matthias,
vielleicht noch nicht optimal (falls es noch mehr Klammerwerte gibt)
aber erstmal tuts es:


Sub Extrahieren()
Dim lLetzte  As Long             ' letzte verwendete Zeile in Spalte A
Dim lZeile   As Long             ' For/Next Lauf-Index
Dim iPos     As Integer          ' Position der Klammer auf
Dim iPosS    As Integer          ' Position der Klammer auf Speicher
Dim iWoPos   As Integer          ' For/Next Lauf-Index
Dim sWoZei   As String * 1       ' Zeichen im zu separierenden Wort
   lLetzte = IIf(Range("A65536") <> "", 65536, Range("A65536").End(xlUp).Row)
   
   For lZeile = 2 To lLetzte     ' ab Zeile 2 bis Ende Daten
      iPos = InStr(1, Range("A" & lZeile).Value, "(") ' Position Klammer auf suchen
      If iPos > 0 Then                             ' Klammer auf vorhanden ?
         iPosS = iPos + 1
         iPos = InStr(iPosS, Range("A" & lZeile).Value, "(") ' Position Klammer auf suchen
         If iPos = 0 Then iPos = iPosS
         Range("B" & lZeile).ClearContents         ' Ergebnis-Zelle leeren
'    Position Klammer auf hochrechnen bis kein Leerzeichen mehr folgt
'    so können dem Klammer auf kein, bis beliebig viele Leerzeichen folgen
         Do
            iPos = iPos + 1
         Loop Until Mid(Range("A" & lZeile).Value, iPos, 1) <> " "
'    die Zeichen ab Position Kunden-Nr einzeln übernehmen und an die neue
'    Zelle übergeben, bis Klammer zu gefunden wird.
         For iWoPos = iPos To Len(Range("A" & lZeile).Value)
            sWoZei = Mid(Range("A" & lZeile).Value, iWoPos, 1)
            If sWoZei <> ")" Then ' auf Klammer zu verproben
               Range("B" & lZeile).Value = Range("B" & lZeile).Value & sWoZei
             Else
               Exit For           ' Leerzeichen gefunden => For/Next verlassen
            End If
         Next iWoPos              ' nächste Wort-Position
      End If
   Next lZeile                    ' nächste Zeile bearbeiten
 
End Sub

     Code eingefügt mit Syntaxhighlighter 2.5



Gruß Peter
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Matthias
Geschrieben am: 10.03.2005 08:24:10
Morgen Peter,
Makro läuft gut! Nur hier hat er noch Probleme:
544. James North (Zimbabwe) (Pvt) Ltd (565415700) - ZIMBABWE
Beim Ergebnis schreibt er mir die Klammer (Pvt) hin.
Kann man das noch ändern?
Das ist ziemlich gut immerhin habe ich über 600 Adressen!!!!!
Danke nochmal
Matthias
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: Peter Feustel
Geschrieben am: 09.03.2005 22:09:02
Hallo Mathias,
obwohl die Lösung von bst genial ist, hier meine verbesserte reine VBA Version,
die beliebig viele Klammerwerte im String bearbeitet - bis der erste mit mindestens
5 Zeichen Länge vorkommt.
https://www.herber.de/bbs/user/19436.xls

Viele Grüße Peter
Bild

Betrifft: AW: Zahl aus Text rausholen!!
von: bst
Geschrieben am: 10.03.2005 07:43:35
Morgen Peter,
Dankeschön.
Gruß, Bernd
Bild

Betrifft: Achtung: Neuer Thread
von: Martin Beck
Geschrieben am: 10.03.2005 10:19:17
Hallo,
damit's nicht langweilig wird, hat Mathias einen neuen Thread eröffnet
https://www.herber.de/forum/messages/582979.html
Gruß
Martin Beck
Bild

Betrifft: Korrektur: inzwischen zwei neue Threads (oT)
von: Martin Beck
Geschrieben am: 10.03.2005 10:21:12

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Tabellen zusammenfuehren"