Microsoft Excel

Herbers Excel/VBA-Archiv

Zahl von Text trennen mit Bedingung


Betrifft: Zahl von Text trennen mit Bedingung von: Josef
Geschrieben am: 16.08.2016 08:59:15

Hallo Zusammen,

ich hoffe ihr könnt mir (wieder mal) helfen.

Ich habe eine Spalte (in meinem Beispiel "Spalte A") da stehen verschiedene Werte (leider auch mit Text).

Nun muss in dieser Spalte nach einem bestimmten Wert gesucht werden (in meinem Beispiel "rot markiert") und der Wert davor ausgelesen werden. Leider ist da mal ein Leerzeichen mal nicht, daher bin ich mit der Excel-Formel '=links() oder =rechts()' nicht wirklich weiter gekommen.

Hier mein Bsp.:
https://www.herber.de/bbs/user/107640.xlsx

Wie das ganze zum Schluss aussehen soll, seht ihr in der Tabelle "Ergebnis".

Es darf auch ein VBA Code sein.

Gruß Josef

  

Betrifft: Reguläre Ausdrücke von: ransi
Geschrieben am: 16.08.2016 10:42:53

Hallo Josef,

ICh glaube nicht das du mit den Standardfunktionen ans Ziel kommst.
Aber mit etwas VBA geht das recht gut.
Schau es dir mal an:
Tabelle1

 ABCDEFG
1       
2  
3Überschrift1FBL%WV
4Haus Kapuzinerstr. 4F 20% 0,17W4F  20%0,17W 
5Haus Ferdinandweg 11 B 70% 16V 11 B 70% 16V
6Gebäude Gem. Felchenstr. 700 F 10 % 125W700 F  10 %125W 
7Fa. Reck-Böhme Hauptstr. 18/2L 70% 125 W  18/2L70%125 W 
8       
9       

Formeln der Tabelle
ZelleFormel
B4=Extract_Spezial($A4;B$3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Dazu muss dieser Code in ein Modul:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit


Public Function Extract_Spezial(strText As String, myPattern As String)
    Dim regex As Object
    Dim objMAtch As Object
    Set regex = CreateObject("VbScript.Regexp")
    With regex
        .Global = True
        .Pattern = "((\d+(,|/)?\d+)|\d+) ?" & myPattern
        If .test(strText) = True Then
            Set objMAtch = .Execute(strText)
            Extract_Spezial = objMAtch(0).Value
            Else:
            Extract_Spezial = ""
        End If
    End With
End Function



ransi


  

Betrifft: AW: Reguläre Ausdrücke von: Josef
Geschrieben am: 16.08.2016 10:49:56

Hi Ransi,

das funktioniert ja fast perfekt. Was noch stört sind die Leerzeichen im Ergebnis. Was muss ich anpassen, damit keine Leerzeichen angezeigt werden?


  

Betrifft: AW: Reguläre Ausdrücke von: ransi
Geschrieben am: 16.08.2016 11:07:44

Hallo Josef,

HAtte ich übersehen.
Geht aber auch.
Tabelle1

 ABCDEFG
1       
2  
3Überschrift1FBL%WV
4Haus Kapuzinerstr. 4F 20% 0,17W4F  20%0,17W 
5Haus Ferdinandweg 11 B 70% 16V 11B 70% 16V
6Gebäude Gem. Felchenstr. 700 F 10 % 125W700F  10%125W 
7Fa. Reck-Böhme Hauptstr. 18/2L 70% 125 W  18/2L70%125W 

Formeln der Tabelle
ZelleFormel
B4=Extract_Spezial($A4;B$3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit


Public Function Extract_Spezial(strText As String, myPattern As String)
    Dim regex As Object
    Dim objMAtch As Object
    Set regex = CreateObject("VbScript.Regexp")
    With regex
        .Global = True
        .Pattern = "((\d+(,|/)?\d+)|\d+)(?= " & myPattern & "|" & myPattern & ")"
        If .test(strText) = True Then
            Set objMAtch = .Execute(strText)
            Extract_Spezial = objMAtch(0).Value & myPattern
            Else:
            Extract_Spezial = ""
        End If
    End With
End Function



ransi


  

Betrifft: AW: Reguläre Ausdrücke von: Josef
Geschrieben am: 16.08.2016 11:19:42

Hallo Ransi,

du bist Klasse... jetzt klappt's. Allerdings hat sich jetzt ein neues Problem aufgetan. Vielleicht kannst du mir auch dabei helfen. Das wäre super.
In Ausnahmefällen kann es passieren, dass vor dem Suchbegriff eine Zahl erscheint die davor wieder einen Buchstaben hat (z. B. Haus Kapuzinerstr. S4F 20% 0,17W), dann darf als Ergebnis nichts drinnen stehen. Hast du auch dafür eine Lösung?

Gruß Josef


  

Betrifft: AW: Reguläre Ausdrücke von: ransi
Geschrieben am: 16.08.2016 11:35:22

Hallo Josef,

Jetzt wirds unübersichtlich...
Teste mal:

Option Explicit


Public Function Extract_Spezial(strText As String, myPattern As String)
    Dim regex As Object
    Dim objMAtch As Object
    Set regex = CreateObject("VbScript.Regexp")
    With regex
        .Global = True
        .Pattern = "([A-Za-zÄÖÜäöü])?(((\d+(,|/)?\d+)|\d+)(?= " & myPattern & "|" & myPattern & "))"
        If .test(strText) = True Then
            Set objMAtch = .Execute(strText)
            If objMAtch(0).submatches(0) Like "[A-Za-zÄÖÜäöü]" Then
                Extract_Spezial = ""
                Else:
                Extract_Spezial = objMAtch(0).Value & myPattern
            End If
            Else:
            Extract_Spezial = ""
        End If
    End With
End Function



ransi


  

Betrifft: AW: Reguläre Ausdrücke von: Josef
Geschrieben am: 16.08.2016 11:41:24

Es funktioniert... Danke dir vielmals...
Ihr seid echt spitze...
Den Rest bekomme ich hoffentlich alleine hin :-)

Gruß Josef


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: Daniel
Geschrieben am: 16.08.2016 11:05:13

Hi
hier mal eine Lösung mit Standardformeln.
https://www.herber.de/bbs/user/107645.xlsx

ich verwende zwei Hilfstabellen, damit die Formeln kurz und übersichtlich bleiben und damit man das Prinzip erkennen kann.

Wenn dich die Hilfstabellen stören, kannst du sie ggf dadurch eleminieren, dass du in der Hauptformel anstelle des Zellbezugs auf die Hilfstabelle die Formel aus der Hilfstabelle einsetzt.
Da die Werte aber mehrfach verwendet werden, wird dann die Hauptformel sehr lang und unübersichtlich.

in der zweiten Hilfstabelle werden Matrixformeln verwendet, hier die Eingabe immer mit STRG+SHIFT+ENTER abschließen.
der Zellbezug $A$1:$A$99 dient als "Schleifenerzeuger" für die Schleife über alle Zeichen im Text.
Das "99" muss ggf angepasst werden, es muss mindestens so gross sein wie der längste Text Zeichen enthält.

Gruß Daniel


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: ransi
Geschrieben am: 16.08.2016 11:11:32

Hallo Daniel,

Hut ab!
Hätte ich nicht gedacht das das möglich ist.

ransi


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: Josef
Geschrieben am: 16.08.2016 11:27:39

Hallo Daniel,

danke für deine Lösung. Die funktioniert ebenfalls. Nur leider wird das ganze etwas unübersichtlich wenn ich mit hunderten von Zeilen und mit ca. 19 Suchbegriffen arbeiten muss. Da ist die Lösung von Ransi einfacher. Trotzdem Danke...

Gruß Josef


  

Betrifft: AW: wer kann die "Original"datei downloaden? ... von: ... neopa C
Geschrieben am: 16.08.2016 12:10:44

Hallo @ all,

... wenn ich den Link zu der von Josef eingestellten Datei aktiviere, wird mir ständig nur www.herber.de/bbs/user/106740.xlsx angeboten. Selbst dann, wenn ich es direkt mit der URL (mit zurück vertauschten Ziffern 67 bzw. 76) versuche, wird mir eine offensichtlich "falsche" (zum thread) Datei bereitgestellt.

Mich interessiert nun, warum das so ist und wie man sich hier bzw. in einem ähnlichen Fall vorgehen kann, um die korrekte Datei zu erhalten. Ransi und David konnten ja offensichtlich auch die richtige Datei herunterladen. Jetzt noch immer?

Gruß Werner
.. , - ...


  

Betrifft: AW: wer kann die "Original"datei downloaden? ... von: Daniel
Geschrieben am: 16.08.2016 12:30:49

Hi
vielleicht ähnliche Gründe, die dazu geführt haben, dass ich bei dir jetzt nicht mehr "Daniel" sondern "David" heiße?

ich kann eine Datei unter dem originallink runterladen, aber es ist jetzt eine andere Datei.

Gruß Daniel


  

Betrifft: AW: sorry für meinen Schreibfehler, Daniel owT von: ... neopa C
Geschrieben am: 16.08.2016 12:33:24

Gruß Werner
.. , - ...


  

Betrifft: Das passiert immer wieder mal, ... von: Luc:-?
Geschrieben am: 16.08.2016 15:07:18

…Werner & all;
primäre Ursache könnte ein (fast) gleichzeitiger bzw relativ schnell nachfolgender anderer UpLoad oder irgendeine ServerAktivität, wobei die Datei verloren geht, sein (ggf auch beim Umbenennen → ZählFehler). Der DownLoad-Service vermutet dann eine FalschAngabe und bietet die vorhandene Datei mit ähnlichster Bezeichnung an. Das hatten wir aber alles schon (mal diskutiert)…
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: danke für Deine Info, doch ... von: ... neopa C
Geschrieben am: 16.08.2016 15:55:26

Hallo Luc,

... bis dato konnte ich, zumindest meiner Erinnerung nach, wenigsten nach Korrektur in der URL-Eingabe auf die Datei zugreifen. Diesmal nicht.

Vielleicht sollten wir Hans nochmal auf diese Problematik hinweisen.

Allerdings ist ja die Problematik mit den fehlenden Beiträge in alten Archivthreads (bei Aktivierung über DEINE BEITRÄGE) sowie nicht mehr funktionierende "gebunkerter" Links auf alte Archivthreads ist ja noch offen und deren Lösung durch Hans wäre sicherlich nicht nur mir wesentlich wichtiger.

Gruß Werner
.. , - ...


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: ransi
Geschrieben am: 16.08.2016 12:24:58

Hallo Werner,


wer kann die "Original"datei downloaden?..

Ich nicht mehr.
Keine Ahnung was da jetzt klemmt.

ransi


  

Betrifft: AW: danke für die Info; weiter offen owT von: ... neopa C
Geschrieben am: 16.08.2016 12:33:37

Gruß Werner
.. , - ...


  

Betrifft: AW: danke für die Info; weiter offen owT von: Josef
Geschrieben am: 16.08.2016 13:30:33

Hallo Werner,

falls du dir das ganze auch mal anschauen möchtest, so habe ich die Datei noch einmal hochgeladen...
Siehe meine letzte Email an Ransi...

Gruß Josef


  

Betrifft: AW: danke für die Info; weiter offen owT von: Daniel
Geschrieben am: 16.08.2016 13:38:14

Hi
vergiss mal Computer und Excel.
Erkläre bitte die genauen Spielregeln, nach denen die Tabelle ausgefüllt werden muss.
So wie wenn du dir einen Ferienarbeiter dafür eingestellt hättest, der einfach nur stur seinen Job nach Anweisung erledigen will, ohne selbst denken zu müssen und der von deiner Arbeit und der Aufgabenstellung keine Ahnung oder Vorkenntnisse hat.
Gruß Daniel


  

Betrifft: AW: danke für die Info; weiter offen owT von: Josef
Geschrieben am: 16.08.2016 15:47:15

Hallo Daniel,

ohne Excel ist es schwer das alles zu erklären. Man muss das zumindest sehen. Daher schlage ich dir vor, du ladest dir meine Excel herunter

https://www.herber.de/bbs/user/107655.xlsx

und versuchst folgendes zu lösen:
In Spalte B3 - G3 stehen Buchstaben, Sonderzeichen oder Zahlen (rote Schrift in der Excel).
Nun musst du nach den einzelnen Zellen (B3 - G3) in der Spalte A suchen. Als Ergebnis sollen alle Zahlen vor und nach dem gesuchten Wert angezeigt werden - bis ein Sonderzeichen, ein Buchstabe oder ein Leerzeichen auftaucht. z. B. Suche Zelle B3 ("F") in Spalte A. Das Ergebnis für Zeile 4 wäre demnach "4F". In Zeile 11 wäre das Ergebnis "4F4".

Ich hoffe du bekommst das hin.

Gruß Josef


  

Betrifft: AW: danke für die Info; weiter offen owT von: Daniel
Geschrieben am: 16.08.2016 16:18:16

Hi
ja das, ist aber genau das, was du erklären musst.

Warum ist in einem Fall das Ergebnis 4F und endet direkt nach dem F?
Warum endet im anderen Fall das Ergebnis nicht nach dem F sondern geht weiter und warum geht es nur bis zur 4 und nicht noch weiter und lautet 4F4/1V ?

das sind die Regeln die du eigentlich kennst (weil du sie ja richtig anwendest), die du aber noch ausformulieren und uns beschreiben musst.

Wenige Beispiele zeigen und uns dann das Erraten der Spielregeln überlassen ist nicht besonders effektiv, weil wir uns dann Spielregeln ausdenken, die zwar auf die gezeigten Beispiele passen, aber nicht auf Fälle, die du uns noch nicht gezeigt hast, und dann muss der Code wieder erweitert und angepasst, oder sogar neu geschrieben werden.
Effektiver ist, wenn du uns die Regeln beschreibst, und wir dir dann sagen, wie du das am besten mit VBA umsetzt.

Gruß Daniel


  

Betrifft: AW: danke für die Info; weiter offen owT von: Daniel
Geschrieben am: 16.08.2016 16:54:39

Hi

ok, probier mal das als Funktion:

Function TeilText(GesamtText As String, SuchText As String) As String
Dim i As Long
Dim Erg As Long
Dim Pos As Long
Dim Pos1 As Long
Dim Pos2 As Long
Dim L As Long

GesamtText = WorksheetFunction.Trim(GesamtText)

Pos = 3
Do
    Pos = InStr(Pos + 1, GesamtText, SuchText, vbBinaryCompare)
    If Pos = 0 Then Exit Do
    If Mid(GesamtText, Pos - 1) Like "#" & SuchText & "*" Then
        Pos1 = Pos - 1
        Exit Do
    End If
    If Mid(GesamtText, Pos - 2) Like "# " & SuchText & "*" Then
        Pos1 = Pos - 2
        Exit Do
    End If
Loop

If Pos = 0 Then
    TeilText = ""
Else
    TeilText = SuchText
    For i = Pos1 To 1 Step -1
        If Not Mid(GesamtText, i, 1) Like "[0-9,.]" Then Exit For
        TeilText = Mid(GesamtText, i, 1) & TeilText
    Next
    For i = Pos + Len(SuchText) To Len(GesamtText)
        If Not Mid(GesamtText, i, 1) Like "[0-9]" Then Exit For
        TeilText = TeilText & Mid(GesamtText, i, 1)
    Next
End If
    

End Function
das ist vielleicht etwas anschaulicher als die Regulären Ausdrücke und du kannst dir den Code dann bei Bedarf selber ein geänderte oder Erweiterte Bedingungen anpassen.

Gruß Daniel


  

Betrifft: AW: danke für die Info; weiter offen owT von: Josef
Geschrieben am: 17.08.2016 06:51:01

Hallo Daniel,

erst einmal danke für dein Code. Nur leider sind meine VBA-Kenntnisse begrenzt und ich komme damit nicht ganz klar. Wo muss ich denn deinen Code einfügen? Modul? Tabelle? Egal wo ich es einfüge - es passiert nichts.

Gruß Josef


  

Betrifft: AW: danke für die Info; weiter offen owT von: Matthias L
Geschrieben am: 17.08.2016 07:32:54

Hallo

Der Code gehört in ein Modul

In der Tabelle musst Du Daniels UDF dann so verwenden wie eine Excelinterne Funktion
Das sieht dann so aus.

Du brauchst die Formel nur in B4 eintragen und dann nach rechts und nach unten ziehen.

Tabelle1

 ABCDEFG
2 Funktion von Daniel
3Überschrift1FBL%WV
4Haus Kapuzinerstr. 4F 20% 0,17W4F  20%0,17W 
5Haus Ferdinandweg 11 B 70% 16V 11B 70% 16V
6Gebäude Gem. Felchenstr. 700 F 10 % 125W700F  10%125W 
7Fa. Reck-Böhme Hauptstr. 18,2L 70% 125 W  18,2L70%125W 
8Fam. Staudacher Raiffeisenstr. 7L7 1% 3.6V  7L71% 3.6V
9Haus Öschweg 88B/4% 0,125W 88B 4%0,125W 
10Gebäude Gros. 3F3/60% 7V3F3  60% 7V
11Haus Siehm 4F4/1V4F4    1V

Formeln der Tabelle
ZelleFormel
B4=TeilText($A4;B$3)
C4=TeilText($A4;C$3)
D4=TeilText($A4;D$3)
E4=TeilText($A4;E$3)
F4=TeilText($A4;F$3)
G4=TeilText($A4;G$3)
B5=TeilText($A5;B$3)
C5=TeilText($A5;C$3)
D5=TeilText($A5;D$3)
E5=TeilText($A5;E$3)
F5=TeilText($A5;F$3)
G5=TeilText($A5;G$3)
B6=TeilText($A6;B$3)
C6=TeilText($A6;C$3)
D6=TeilText($A6;D$3)
E6=TeilText($A6;E$3)
F6=TeilText($A6;F$3)
G6=TeilText($A6;G$3)
B7=TeilText($A7;B$3)
C7=TeilText($A7;C$3)
D7=TeilText($A7;D$3)
E7=TeilText($A7;E$3)
F7=TeilText($A7;F$3)
G7=TeilText($A7;G$3)
B8=TeilText($A8;B$3)
C8=TeilText($A8;C$3)
D8=TeilText($A8;D$3)
E8=TeilText($A8;E$3)
F8=TeilText($A8;F$3)
G8=TeilText($A8;G$3)
B9=TeilText($A9;B$3)
C9=TeilText($A9;C$3)
D9=TeilText($A9;D$3)
E9=TeilText($A9;E$3)
F9=TeilText($A9;F$3)
G9=TeilText($A9;G$3)
B10=TeilText($A10;B$3)
C10=TeilText($A10;C$3)
D10=TeilText($A10;D$3)
E10=TeilText($A10;E$3)
F10=TeilText($A10;F$3)
G10=TeilText($A10;G$3)
B11=TeilText($A11;B$3)
C11=TeilText($A11;C$3)
D11=TeilText($A11;D$3)
E11=TeilText($A11;E$3)
F11=TeilText($A11;F$3)
G11=TeilText($A11;G$3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Mein Respekt an Daniel!

Gruß Matthias


  

Betrifft: AW: danke für die Info; weiter offen owT von: Daniel
Geschrieben am: 17.08.2016 07:49:52

Du musst mit diesem Code das gleiche machen, was du mit dem Code von Ransi gemacht hast.
Bei seinem Code hast du es ja auch geschafft, daher bin ich davon ausgegangen, dass ich dir das nicht noch einmal erklären muss.
Gruß Daniel


  

Betrifft: AW: danke für die Info; weiter offen owT von: Josef
Geschrieben am: 17.08.2016 08:37:07

Hallo Daniel,

Ransi hatte mir die Formel für meine Excel genannt. Jedenfalls funktioniert das ganze jetzt so, wie ich es benötige. Ich danke Dir dafür.

Gruß Josef


  

Betrifft: AW: danke für die Info; weiter offen owT von: Daniel
Geschrieben am: 17.08.2016 09:19:01

Hi
ich habe dir auch die Formel für dein Excel genannt.
Formelcode in ein allgemeines Modul eintragen und dann die Formel in der Tabelle verwenden funktioniert genau so.
Gruß Daniel


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: Josef
Geschrieben am: 16.08.2016 13:27:59

Hallo Ransi,

ich hoffe du kannst mir noch einmal helfen. Es gibt noch weitere wenige Ausnahmen, wo dein VBA nicht funktioniert (mit grüner Schrift in der Excel hinzugefügt). Hoffentlich kannst du mir auch dabei noch einmal helfen.
Da man die Datei wohl nicht mehr runterladen kann, bekommst hier noch einmal einen link:

https://www.herber.de/bbs/user/107648.xlsx

Fehle im VBA: Wenn man z. B. nach "L" sucht, dann darf er auch nur nach L suchen. Dein Vba gibt mir aber auch einen Wert zurück wenn z. B. in 27LF in der Spalte A vorkommt.

Gruß Josef


  

Betrifft: AW: Zahl von Text trennen mit Bedingung von: ransi
Geschrieben am: 17.08.2016 10:16:51

Hallo Josef,

Gib uns doch mal ein Beispiel mit allen Eventualitäten.
Nachträglich etwas in ein Regex reinzufrickeln ist immer blöd.

ransi


Beiträge aus den Excel-Beispielen zum Thema "Zahl von Text trennen mit Bedingung"