Microsoft Excel

Herbers Excel/VBA-Archiv

Wort in Text suchen und erste Zahl davor ausgeben

Betrifft: Wort in Text suchen und erste Zahl davor ausgeben von: Stefan
Geschrieben am: 07.09.2004 09:19:15

Guten Morgen zusammen,

ich habe folgendes Problem:
Ich habe in einer Spalte einen Text, der unterschiedlich lang sein kann. Jedoch kommt immer das Wort "netto" vor. Ich möchte nun die erste Zahl, die im Text vor "netto" steht ausgegeben haben, also z.B.:
xxxxxxxx12xxxxxnettoxxxxxxxxxxxxx
Jetzt sollte die 12 ausgegeben werden.

Wie stelle ich das an?

Danke schon mal,

steve1da

  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: ExceL
Geschrieben am: 07.09.2004 11:23:42

hi

 AB
1xxxxxxx12xxxtttxxnettoxxxxxxxxxxxxx12
2xxxx12xxnettoxxxxxxx12
3der wert der 41 hsabjasb netto fdf41
4vhgfc 177791 hsabb netto177791
5uieurhzf 5445 ierjrriejr   netto jhjhf5445
612345 hhhhh netto hnhh12345





 AB
1xxxxxxx12xxxtttxxnettoxxxxxxxxxxxxx{=TEIL(A1;MAX(WENN(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1)-1);ZEILE($1:100);1)*1));ZEILE($1:100);0);A1)-SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1))}
2xxxx12xxnettoxxxxxxx{=TEIL(A2;MAX(WENN(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2)-1);ZEILE($1:101);1)*1));ZEILE($1:101);0);A2)-SUMME(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2));ZEILE($1:101);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2));ZEILE($1:101);1)*1))*1))}
3der wert der 41 hsabjasb netto fdf{=TEIL(A3;MAX(WENN(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3)-1);ZEILE($1:102);1)*1));ZEILE($1:102);0);A3)-SUMME(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3));ZEILE($1:102);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3));ZEILE($1:102);1)*1))*1))}
4vhgfc 177791 hsabb netto{=TEIL(A4;MAX(WENN(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4)-1);ZEILE($1:103);1)*1));ZEILE($1:103);0);A4)-SUMME(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4));ZEILE($1:103);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4));ZEILE($1:103);1)*1))*1))}
5uieurhzf 5445 ierjrriejr   netto jhjhf{=TEIL(A5;MAX(WENN(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5)-1);ZEILE($1:104);1)*1));ZEILE($1:104);0);A5)-SUMME(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5));ZEILE($1:104);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5));ZEILE($1:104);1)*1))*1))}
612345 hhhhh netto hnhh{=TEIL(A6;MAX(WENN(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6)-1);ZEILE($1:105);1)*1));ZEILE($1:105);0);A6)-SUMME(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6));ZEILE($1:105);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6));ZEILE($1:105);1)*1))*1))}





was noch nicht geht ist wenn 2 Zahlen im String sind#

Gruß
Christian


  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: Stefan
Geschrieben am: 07.09.2004 11:32:13

Hallo Christian,

genial! Genau das was ich suche. Momentan kommt es auch nicht vor, dass 2 Zahlen im String sind. Wenn doch, meld ich mich nochmal.

Vielen lieben Dank,

steve1da


  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: Stefan
Geschrieben am: 07.09.2004 11:50:47

Hallo Christian,

leider ist es teilweise doch so, dass mehr als 1 Zahl im String ist. Dann klappt Deine Formel zwar auch, er zeigt die richtige Zahl an, jedoch nimmt er in diesen Fällen auch immer 1-3 Buchstaben, die vor der Zahl stehen, mit. Kann dann mit der Zahl natürlich nicht weiterrechnen.
Noch ne Idee?

Steve1da


  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: nighty
Geschrieben am: 07.09.2004 12:26:38

hi alle :)

dann vielleicht so :)

10-25 zeichen wird zur zeit eine zahl gesucht,reicht auch prima daumen angaben :)

gruss nighty

Rem =Sumtext(A1:A4;10;25)

einzufuegen unter alt f11(vbeditor)/einfuegen/modul

Function Sumtext(Zellen As Range, AnfangX As Long, EndeX As Long) As Double
Dim Zelle As Range
Dim zahl1
Dim zahl2
Application.Volatile
For Each Zelle In Zellen
If EndeX > Len(Zelle) Then EndeX = Len(Zelle)
If AnfangX < 1 Then AnfangX = 1
For zeich1 = AnfangX To EndeX
If Asc(Mid$(Zelle, zeich1, 1)) > 47 And Asc(Mid$(Zelle, zeich1, 1)) < 58 _
Or Asc(Mid$(Zelle, zeich1, 1)) = 44 Or Asc(Mid$(Zelle, zeich1, 1)) = 46 Then
zahl1 = zahl1 & Mid$(Zelle, zeich1, 1)
End If
Next zeich1
If zahl1 = "" Then zahl1 = "0"
zahl2 = zahl1 * 100
Sumtext = Sumtext + (zahl2 / 100)
zahl1 = ""
zahl2 = ""
Next
End Function

bei nicht haendischer eingabe vielleicht noch dieses zur ergaenzung

einzufuegen unter alt f11(vbeditor)/projektexplorer/DeineTabelle

Private Sub Worksheet_Change(ByVal Target As Range)
Application Calculate
End Sub



  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: ExceL
Geschrieben am: 07.09.2004 13:00:13

hi

aber nur mit einer Hilsspalte
:-(
ansonsten wäre natürlich VBA eine lösung

 ABC
1xxxxxxx12xxxtttxxnettoxxxxxxxxxxxxx{=TEIL(A1;MAX(WENN(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1)-1);ZEILE($1:100);1)*1));ZEILE($1:100);0);A1)-SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B1;ZEILE($1:50);1)*1);ZEILE($1:50)))<2;B1*1;RECHTS(B1;1+LÄNGE(B1)-MIN(WENN(ISTZAHL(TEIL(B1;ZEILE($1:50);1)*1);ZEILE($1:50)))))*1}
2xxxx12xxnettoxxxxxxx{=TEIL(A2;MAX(WENN(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2)-1);ZEILE($1:101);1)*1));ZEILE($1:101);0);A2)-SUMME(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2));ZEILE($1:101);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A2;FINDEN("netto";A2));ZEILE($1:101);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B2;ZEILE($1:51);1)*1);ZEILE($1:51)))<2;B2*1;RECHTS(B2;1+LÄNGE(B2)-MIN(WENN(ISTZAHL(TEIL(B2;ZEILE($1:51);1)*1);ZEILE($1:51)))))*1}
3der wert der 41 hsabjasb netto fdf{=TEIL(A3;MAX(WENN(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3)-1);ZEILE($1:102);1)*1));ZEILE($1:102);0);A3)-SUMME(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3));ZEILE($1:102);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A3;FINDEN("netto";A3));ZEILE($1:102);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B3;ZEILE($1:52);1)*1);ZEILE($1:52)))<2;B3*1;RECHTS(B3;1+LÄNGE(B3)-MIN(WENN(ISTZAHL(TEIL(B3;ZEILE($1:52);1)*1);ZEILE($1:52)))))*1}
4vh4g88fc 177791 hsabb netto{=TEIL(A4;MAX(WENN(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4)-1);ZEILE($1:103);1)*1));ZEILE($1:103);0);A4)-SUMME(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4));ZEILE($1:103);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A4;FINDEN("netto";A4));ZEILE($1:103);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B4;ZEILE($1:53);1)*1);ZEILE($1:53)))<2;B4*1;RECHTS(B4;1+LÄNGE(B4)-MIN(WENN(ISTZAHL(TEIL(B4;ZEILE($1:53);1)*1);ZEILE($1:53)))))*1}
5uieurhzf 5445 ierjrriejr   netto jhjhf{=TEIL(A5;MAX(WENN(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5)-1);ZEILE($1:104);1)*1));ZEILE($1:104);0);A5)-SUMME(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5));ZEILE($1:104);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A5;FINDEN("netto";A5));ZEILE($1:104);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B5;ZEILE($1:54);1)*1);ZEILE($1:54)))<2;B5*1;RECHTS(B5;1+LÄNGE(B5)-MIN(WENN(ISTZAHL(TEIL(B5;ZEILE($1:54);1)*1);ZEILE($1:54)))))*1}
612345 hhhhh netto hnhh{=TEIL(A6;MAX(WENN(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6)-1);ZEILE($1:105);1)*1));ZEILE($1:105);0);A6)-SUMME(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6));ZEILE($1:105);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A6;FINDEN("netto";A6));ZEILE($1:105);1)*1))*1))}{=WENN(MIN(WENN(ISTZAHL(TEIL(B6;ZEILE($1:55);1)*1);ZEILE($1:55)))<2;B6*1;RECHTS(B6;1+LÄNGE(B6)-MIN(WENN(ISTZAHL(TEIL(B6;ZEILE($1:55);1)*1);ZEILE($1:55)))))*1}





Gruß
Christian


  


Betrifft: AW: Wort in Text suchen und erste Zahl davor ausgeben von: Stefan
Geschrieben am: 07.09.2004 13:27:24

Hallo Christian,

das mit der Hilfsspalte funktioniert auch super.
Habe in der Zwischenzeit Deine erste Formel wie folgt ergänzt:
{=rechts(TEIL(A1;MAX(WENN(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1)-1);ZEILE($1:100);1)*1));ZEILE($1:100);0);A1)-SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1)+1;SUMME(ISTZAHL((TEIL(LINKS(A1;FINDEN("netto";A1));ZEILE($1:100);1)*1))*1));2}

Dadurch wurden nur die Zahlen angezeigt, die Buchstaben verschwanden.

Trotzdem nochmals 1000 Dank,

steve1da


 

Beiträge aus den Excel-Beispielen zum Thema "Wort in Text suchen und erste Zahl davor ausgeben"