Wert ab einer stelle in der Zelle wiedergeben

Bild

Betrifft: Wert ab einer stelle in der Zelle wiedergeben
von: Matthias Dietz
Geschrieben am: 23.11.2015 00:28:49

Guten abend liebe Foren-Gemeinde,
Habe erneut ein Problem oder ich denke falsch.
Habe eine Liste in Spalte B die diveres Mengenangaben stehen.
folgendes habe ich mir zusammengebastelt:
=(WENN(ISTFEHLER(SUCHEN("ltr";$B2))=WAHR;SUCHEN("kg";$B2);SUCHEN("ltr";$B2)))
Nun weis ich ja wo das gesuchte Wort steht. Jetzt möchte ich aber, dass der Wert davor ausgegeben wird. Der Inhalt der Zelle ist variabel (zb: "25 ltr/30 kg", "Eimer Inhalt: 3 kg"; "Chlorit 7,5% 30 ltr" oder einfach nur "1,0 ltr"
Bevorzugt wird die ltr-angabe. Ich benötige die Zahl, um eine Liste zu erstellen, wieviel wir von dem Produkt gesamt (mit verschiedenen Verpackungsgrößen) versendet haben.
BSP. Tabelle:
https://www.herber.de/bbs/user/101734.xlsx
Vielen Dank im Vorraus für eure Hilfe.
LG Matthias
Das dient dazu, das ich eine

Bild

Betrifft: Das dürfte mit normalen Formeln recht ...
von: Luc:-?
Geschrieben am: 23.11.2015 05:00:04
…kompliziert wdn, Matthias,
deshalb schlage ich folgende Lösung mit UDF-haltigen Fmln vor (UDF=eigene Funktion):

 ABCDEF
1ProduktMengeltrkgltrkg
2Aktiv-P1,0 ltr1,0 WAHR 
3Aktiv-P10 ltr10,0 WAHR 
4Aktiv-P30 ltr30,0 WAHR 
5Aqua Puro"N" Eimer Inhalt 2 kg 2 WAHR
6Aqua Tivo5 kg 5 WAHR
7Aqua TuboCL2 10 kg 10 WAHR
8Aqua TuboFO 10 kg 10 WAHR
9Biofilmentferner10 ltr10,0 WAHR 
10Chlordioxid Stammlösung10 kg 10 WAHR
11Eisen-III-Chlorid-Lösung40% 40 kg 40 WAHR
12FlorazonChlorit 7,5% 30 ltr30,0 WAHR 
13Natriumhypochlorit 13%10 ltr /12 kg10,012WAHRWAHR
14Natriumhypochlorit 13%30 ltr /35 kg30,035WAHRWAHR
15Natriumhypochlorit 13%60 ltr/70 kg60,070WAHRWAHR
16Natronlauge50% 45 kg 45 WAHR
17Natronlauge25% 30 kg 30 WAHR
18Natronlauge25% 60 kg 60 WAHR
19Optipo-N1,0 kg 1 WAHR
20Optipo-NEimer Inhalt: 3 kg  3 WAHR
21Oxys1,0 ltr1,0 WAHR 
22Oxys10 ltr10,0 WAHR 
23Oxys30 ltr30,0 WAHR 
24Oxys-K8,3 ltr/10 kg8,310WAHRWAHR
25Oxys-K25 ltr/30 kg25,030WAHRWAHR
26Salzsäure31/33% 30 kg 30 WAHR
27Folmar(IBC) 1300 kg 1300 WAHR
28F3232(IBC) 1000 ltr1000,0 WAHR 
29    Kontrolle
Die Fml dafür ist eine 2zellige MatrixFml über eine Zeile der Spalten C:D und kann nach unten gezogen wdn. Sie lautet:
C2:D2[;C3:D28]:{=VSplit(WECHSELN(WAHL(ISTZAHL(SUCHEN("ltr*/";B2))+(RECHTS(GLÄTTEN(B2);2)="kg")+1;"# ";" #";"#");"#";
VJoin(WENN(ISTZAHL(VSplit(WECHSELN(WECHSELN(B2;" /";"/");"ltr/";"-");;1));
ABS(VSplit(WECHSELN(WECHSELN(B2;" /";"/");"ltr/";"-");;1));"");;-1));;1)}
Die beiden UDFs VJoin und VSplit sind in dieser Datei des Archivs zu finden.
Übrigens muss man eine Wenn-Bedingung nicht mit WAHR vergleichen, schon gar nicht, wenn das Ergebnis ohnehin nur WAHR oder FALSCH lauten kann…! ;-]
Morrn, Luc :-?

Besser informiert mit …

Bild

Betrifft: Literzahl extrahieren
von: WF
Geschrieben am: 23.11.2015 07:29:41
Hi,
in D2 folgende Arrayformel:
{=WENNFEHLER(RECHTS(LINKS(B2;FINDEN("ltr";B2)-1);ANZAHL(RECHTS(LINKS(B2;FINDEN("ltr";B2)-1); SPALTE($A:$Z))*1))*1;"") }
runterkopieren
Salut WF

Bild

Betrifft: geht sogar einfacher
von: WF
Geschrieben am: 23.11.2015 07:49:54
Hi,
in D2:
=WENNFEHLER(VERWEIS(9^9;1*RECHTS(LINKS(B2;FINDEN("ltr";B2)-1);SPALTE(A:Z)));"")
runterkopieren
WF
siehe:
http://excelformeln.de/formeln.html?welcher=101

Bild

Betrifft: ...und dann auch noch fehlerhafter, ...
von: Luc:-?
Geschrieben am: 23.11.2015 10:19:15
…Walter;
deine MatrixFml liefert „nur“ 5 Fehler bei kg (in Zeilen 13:15;24:25), deine einfachere dann 1 bei ltr (in Zeile 12) und 5 bei kg, aber an anderen Stellen (in Zeilen 11;16:18;26).
Wat nu?!
Gruß, Luc :-?

Bild

Betrifft: na ja: Zeile 12 war mir auch aufgefallen
von: WF
Geschrieben am: 23.11.2015 10:39:37
... und kg hatte ich nicht ausprobiert.
Da geht /Zahl (ohne Leerzeichen dazwischen) in die Hose.
WF

Bild

Betrifft: wenn Du wechselst, kann ich das auch
von: WF
Geschrieben am: 23.11.2015 10:57:39
Für die aufgeführten Beispiele müsste es stimmen:
=WENNFEHLER(VERWEIS(9^9;1*RECHTS(LINKS(WECHSELN(WECHSELN(B2;"/";"#");"%";"#");FINDEN("ltr";B2)-1); SPALTE(A:Z)));"")
WF

Bild

Betrifft: Aller guten Dinge sind 3! Hier nun die Nr 3, ...
von: Luc:-?
Geschrieben am: 23.11.2015 11:14:28
…gleichzeitig der Kürze-Favorit, Walter,
sofern nicht noch schlimmere Kombis vorkommen können/wdn. Aber dann müsste ich wohl auch neu überlegen, abgesehen mal davon, dass meine jetzige Fml evtl auch noch kürzbar ist. Ist halt so'ne Sache mit derartigen Primärdaten… ;-)
Luc :-?

Bild

Betrifft: Ich kann übrigens auch ohne WECHSELN, ...
von: Luc:-?
Geschrieben am: 23.11.2015 13:41:57
…Walter (& Matthias),
auch mit 2 UDFs, aber MaskOn statt VJoin und ohne MatrixFml in EinzelZellen:
S2[:S28;T2:T28]:=WENNFEHLER(--INDEX(VSplit(MaskOn($B2;"num";","&C$1));VERGLEICH("*"&S$1;VSplit(MaskOn($B2;"num";","&C$1));0)-1);"") → im SpaltenKopf (C1:D1) stehen die ME ltr u. kg
Das wären dann irrelevante 3 Zeichen und 1 Fkt weniger* als bei deiner fktionierenden Variante… ;-)
Beide UDFs sind per RECHERCHE als direkt geposteter PgmCode im Archiv zu finden (bei MaskOn Fassung von 2014 verwenden!).
* Daniels spezielle UDF ist hierin natürlich konkurrenzlos, aber eben auch nur für so etwas o.Ä. zu verwenden.
Luc :-?

Bild

Betrifft: AW: Ich kann übrigens auch ohne WECHSELN, ...
von: Matthias Dietz
Geschrieben am: 23.11.2015 17:28:14
Hallo Forengemeinde,
Ich danke recht herzlich für die rege Teilnahme.
Diese Formel hatte ich schon gestern auspropiert, funktioniert leider nur bedingt. (hatte ich gestern schon im Netz gefunden.
=WENNFEHLER(RECHTS(LINKS(B2;FINDEN("ltr";B2)-1);ANZAHL(RECHTS(LINKS(B2;FINDEN("ltr";B2)-1); SPALTE($A:$Z))*1))*1;"")
Da die Zellen sehr unterschiedlich vom Text her sind, funktioniert die Lösung von Luc-? ausgezeichnet.
Danke für diese Lösung (und ich danke auch für den Lerneffekt, das man zusätzlich Funktionen einbringen kann)
Vielen Dank für eure Hilfe,
Liebe Grüße Matthias

Bild

Betrifft: Bitte sehr, gern geschehen! ;-) owT
von: Luc:-?
Geschrieben am: 23.11.2015 21:13:48
:-?

Bild

Betrifft: AW: Lösung mit UDF
von: Daniel
Geschrieben am: 23.11.2015 11:52:02
Hallo
ich würde hier auch dazu raten, hier eine UDF zu erstellen, welche die gewünschten Werte aus dem Gesamttext extrahiert.
In einer Speziellen UDF lassen sich die Besonderheiten der Ausgangstexte relativ einfach berücksichtigen.
https://www.herber.de/bbs/user/101743.xlsm
die folgende UDF gibt aus einem längeren Text dasjenige Wort oder Zahl zurück, welches vor dem Teiltext steht, welcher im zweiten Parameter angegeben ist:
=WertVorEinheit("xxxxx 30 ltr xxxx";"ltr") ergibt 30

Function WertVorEinheit(Gesamttext As String, Einheit As String) As Variant
Dim TeilTexte
Dim i As Long
Gesamttext = Replace(Gesamttext, "/", " ")
Gesamttext = Replace(Gesamttext, Einheit, " " & Einheit)
Gesamttext = WorksheetFunction.Trim(Gesamttext)
TeilTexte = Split(Gesamttext, " ")
For i = 1 To UBound(TeilTexte)
    If TeilTexte(i) = Einheit Then
        WertVorEinheit = TeilTexte(i - 1)
        If IsNumeric(WertVorEinheit) Then WertVorEinheit = CDbl(WertVorEinheit)
        Exit For
    End If
Next
End Function
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Wert ab einer stelle in der Zelle wiedergeben"