Zahl aus Text rausfiltern?

Computer: Software & Programmierung Office Software
1

Ich habe hier eine (nicht von mir angelegte) Liste (gut 500 Zeilen) mit mehreren Spalten, eine davon gibt die Stückzahl an, eine den Preis pro Stück in Euro.

Problem: die Liste wurde von verschiedenen Leuten ueber einen laengeren Zeitraum erstellt - und einige füllte sie so aus, dass in der jeweiligen Zelle nicht nur der Wert, sondern auch die Einheit steht (also in der Spalte „Stück“ steht dann „30St“, manchmal auch „30 St“ statt des bloßen Wertes „30“; Analog die Spalte „Preis“: manchmal steht da „4,00 EUR“, manchmal „4,00 €“, manchmal „4 EUR“, manchmal „4EUR“ usw. usw. - und manchmal natuerlich auch der Zahlenwert „4,00“, ohne Text)

Das einzig Ermutigende ist, dass der enthaltene „Zahlenwert“ selbst immer der richtige ist. Und in pro Zelle steht immer nur *ein* Zahlenwert.

Da Zellen, in denen Text mit eingetragen wird, von Excel als Text behandelt werden, kann ich mit all diesen Werten nicht rechnen (nicht multiplizieren, keine Summe bilden etc.)

Gibt es eine Möglichkeit, aus einer „Textzelle“ den enthaltenen Zahlenwert „rauszufiltern“ und in einen rechnerisch weiter verarbeitbaren Zahlenwert umzuwandeln? Mit anderen Worten: eine Methode, die aus „4,00 EUR“,„4,00 €“, „4 EUR“ und „4EUR“ stets den Zahlenwert „4“ macht?

Das wuerde mir *sehr viel* „Handarbeit“ ersparen… :smile:

Ulrich

2

Gibt es eine Möglichkeit, aus einer „Textzelle“ den
enthaltenen Zahlenwert „rauszufiltern“ und in einen
rechnerisch weiter verarbeitbaren Zahlenwert umzuwandeln? Mit
anderen Worten: eine Methode, die aus „4,00 EUR“,„4,00 €“, „4
EUR“ und „4EUR“ stets den Zahlenwert „4“ macht?

Das wuerde mir *sehr viel* „Handarbeit“ ersparen… :smile:

Ulrich

Hallo Ulrich,

ersetze mit „Bearbeiten–>Ersetzen“ in der Spalte mit Stückzahlen ‚St‘ durch nichts (kein Eintrag für ‚Ersetzen durch‘).
In gleicher Weise in der Spalte Preis die Währungseinheiten durch nichts ersetzen. Danach sollte Excel mit den Zahlen rechnne können.

Gruß
Franz

3

Zunaechst mal Danke. Mit dieser Methode bin ich bisher vorgegangen. Leider ist aber die Kreativität der Menschen größer als meine Phantasie (es gibt z.B. auch „Spezialisten“, die „St: 30“ in die Spalte „Stueck“ eintragen). Ich habe noch etliche solcher Listen vor mir; und bei 500+ Zeilen ist es ziemlich mühsam, alle „Varianten“ zu ermitteln. Natürlich *geht* es so - ist aber immer noch ziemlich mühsam und aufwendig.

(Eine kleine Feinheit am Rande: diese Methode löscht dann auch in den von mir geschaffenen Formelfeldern alle "e"s oder "st"s; wenn ich z.B. die "4 e"s oder "4E"s korrigieren will, steht dann plötzlich in der Formel „SUMM(C3:C510)“ statt „SUMME(C3:C510)“. Klar, kann man auch umgehen, wird dann aber immer umständlicher)

Deshalb nochmal meine Frage: gibt es irgendeinen Formelausdruck, der die Zahlenwerte aus den „Textzellen“ rausholt?

Ulrich

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

4

Hallo, Ulli!

Was die Phantasie der Leute angeht: Wem sagst Du das. Habe gerade eine Übersetzung zurückbekommen, in der die Anzahl der verschiedenen Schreibweisen für „Stück“, im Deutschen schon astronomisch, im Englischen noch getoppt wurden.

Ob es eine Formel für Dich gibt, weiß ich nicht. Vermute nein. Daher wirst Du um ein kleines Marko nicht herumkommen. Frage: Finden sich auch nette Exponentialwerte in Deiner Tabelle wie „2.3E3 DM“? Falls ja, wird’s komplizierter. Auch, wenn die Jungs munter formatierende Zeichen wie „.“ als Tausender-Trennzeichen, am besten noch unterschiedlich (also mal 1.200,00 € und mal 1,200,200.00 $), eingetragen haben, wird’s arg ungemütlich.

Zum Umrechnen: Schreib Dir einfach eine Funktion, die von vorne anfangend nur Zahlen zulässt und beim ersten Nicht-Zahl-Zeichen abbricht. Die sieht ungefähr so aus:

Function ZahlString(pstrText As String) As String
 Dim intI As Integer
 intI = 1
 ' zugelassen sind alle Ziffern, Komma und Tausender-Trennzeichen
 ' hier je nach Ländereinstellungen bei der Umrechnung aufpassen!
 Do While intI 0
 intI = intI + 1
 Loop
 ZahlString = Left$(pstrText, intI - 1)
End Function

Dann rennst Du über Deine Spalte und schreibst die Resultate am besten in eine andere, um die Korrektheit zu überprüfen und die Eingaben nicht zu verlieren. Das geht dann ungefähr so: (Abbruchkriterien wie gehabt anpassen…)

Sub Anpassen()
 Dim lngZeile As Long
 Dim strZahl As String
 lngZeile = 1
 Do While ActiveSheet.Cells(lngZeile, 1) ""
 ' Trim, um führende Leerzeichen zu ignorieren
 strZahl = ZahlString(trim(ActiveSheet.Cells(lngZeile, 1)))
 If IsNumeric(strZahl) Then
 ActiveSheet.Cells(lngZeile, 2) = CDbl(strZahl)
 Else
 ActiveSheet.Cells(lngZeile, 2) = strZahl
 End If
 lngZeile = lngZeile + 1
 Loop
End Sub

Wie immer ungetestet/unkompliliert; Feinheiten bei der Behandlung müssen u. U. auch vorgenommen werden (siehe „1.200,00“ etc., Vorzeichen usw…)

Gruß, Manfred

5

Hallo Ulrich,

eine Funktion in Excel kenne ich auch nicht, aber wie wäre es, die Spalte nach Word zu kopieren und dort mit Ersetzen - Erweitern - Sonstiges - Beliebiger Buchstabe alle Buchstaben zu ersetzen - und dann evtl. noch „.“ bzw. „:“ - dann werden auch evtl. vorhandene Formeln nicht verändert. - Und dann natürlich wieder zurück nach Excel.

Viel Erfolg

Karin

6

Zunaechst noch mal: Danke an Franz, Karin, Manfred.

–> Manfred: ich hab’s fast schon befürchtet - werd ich also wohl meine seit Jahren „eingemotteten“ (und sicher inzwischen veralteten) VBasic-Kenntnisse wieder rauskramen müssen…
Schaden tut’s mir sicher nicht (wollte ich sowieso seit Langem schon tun), aber das bedeutet, dass ich mich mal an einem Wochenende dransetzen werde (die nächsten drei sind allerdings schon verplant…)

Auf jeden Fall danke für den Tipp - eine Lösung muss gefunden werden (werde zunächst mal Karin’s Tipp ausprobieren, aber auf die Dauer ist das auch etwas umständlich… danke trotzdem!), und in ein paar Wochen werd ich dann die VBasic-Variante angehen.

Nochmal herzlichen Dank und ein schönes Wochenende!

Ulrich

Hallo, Ulli!

Was die Phantasie der Leute angeht: Wem sagst Du das. Habe
gerade … …
…
Gruß, Manfred

7

(Nachtrag)
Hab vergessen, deine Frage zu beantworten: Nein, Exponentialfunktionen und Tausender-Trennzeichen sind nicht enthalten (der höchste enthaltene Einzelpreis liegt irgendwo bei 50 Euro). Und ich *hoffe*, dass nirgends Punkte statt Kommas (4.00 statt 4,00) versteckt sind - bisher hab ich keine entdeckt…

Lediglich *alle* möglichen „Text“-Varianten wie 4 Euro, 4Euro, Euro 4, EUR 4, E 4, € 4, 4 EUR, 4E, 4 €, 4€ usw. usw.

In der Spalte „Stück“ ist es ähnlich…

Ach ja: und falls *doch* noch jemand eine Formel weiss… :wink:

Nochmal schönes Wochenende!

Ulrich

8

Hallo Ulrich
Habe noch ein wenig probiert und eine benutzerdefinierte Funktion gebastelt.
Die sollte deine verschiedenen Einträge bereinigen können.

Function Bereinigen(EingabeText)
 Text = EingabeText
'Sonderfall: als erstes steht ein Komma z.B. ,50 EUR
 If Left(Text, 1) = "," Then GoTo weiter1
'linke Texte abschneiden
 Do Until Asc(Left(Text, 1)) \>= 48 And Asc(Left(Text, 1)) = 48 And Asc(Right(Text, 1)) 

Wünsche ein schönes Wochenende

Franz


> <small>[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]</small>
9

Hier noch ein Vorschlag, der zwar auf den ersten Blick etwas Handarbeit erfordert, jedoch ziemlich geht:

Beide Spalten (Preis/Anzahl) sind am besten getrennt zu behandeln, das Vorgehen ist aber bei beiden wie folgt:

  1. Die Spalte transponieren (Kopieren, dann „Inhalte Einfügen“ „transponieren“ anticken. Ergebnis: Die Werte stehen alle in Zeile 1 nebeneinander. Da XL nur 256 Spalten hat, muss die Liste in zwei zerlegt werden.

  2. In der ersten freien Spalte nach den Werten (also z.B. bei 255 Werten die Spalte 256=IV) trägst du ab zeile 3 alle möglichen Werte untereinander ein - d.h. natürlich nicht eintragen, sondern mit „Reihe auffüllen“. Kommt nur 1-50 vor, hast du also 50 Zeilen untereinander, in denen halt 1,2,3…50 steht. Kommen beim Betrag Dezimalbrüche vor, kannst du die Reihe mit 1,00, 1,01, 1,02…50,00 auffüllen. Beide Varianten benötigen ja nur ein Klick und genug Zeilen gibt’s ja…

  3. Ab der dritten Zeile unter jedem Listenwert kommt folgende Formel rein, die dann so weit runterkopiert wird, wie es „Wertmöglichkeiten“ in der Spalte IV gibt (also z.B. von a3-a54 bei ganzzahligen Werten von 0-50):

=WENN(ISTFEHLER(SUCHEN($IV3;A$1))=FALSCH;$IV3)

Wenn die jeweilige Wertmöglichkeit in dem Listenwert (A1) vorhanden ist, steht sie dann in dieser Formelzelle.

Natürlich wird bei einem Wert von z.B. 43 sowohl erkannt, dass eine 4, 3 und 43 in dem Text vorhanden ist. Deswegen kommt in Zeile a2 die Formel:

=MAX(A3:A54)

Und dies ist dann dein „Zahlenextrakt“…