Wie wählt man die erste Zahl in einer Zelle in Excel aus?

1202
jeff

Ich habe eine Tabelle, die sowohl eine Zahl als auch etwas Text in einer Zelle speichert, und ich möchte eine auf den Zahlen basierende Arithmetikoperation ausführen. So sieht mein Tisch so aus:

Number1 | Number2 | Product 2 | 3 | 6 3 (some text) | 40 | 120 4 | 5 (here too) | 20

In Gegenwart dieser hässlichen Texte möchte ich daher immer noch in der Lage sein, Spalte 3 automatisch zu produzieren. Ich denke, ich brauche eine Funktion, die den Teilstring bis zum ersten nicht-numerischen Zeichen in eine Zahl zerlegt. Kann ich das mit Excel machen?

PS: Die Nummern können mehr als 1 Ziffern haben. Es kann davon ausgegangen werden, dass sie ganze Zahlen sind, aber es wäre schön, eine Lösung zu sehen, die auch für Fließpunkte gilt :)

Vielen Dank,

1
Die ideale Lösung ist natürlich, Zahlen und Text nicht in einer Zelle zu mischen, die Sie für die Arithmetik verwenden möchten. Ist die Aufteilung der ersten beiden Spalten nicht in Frage? fixer1234 vor 7 Jahren 0
Mit Aufteilen meinen Sie, die Zahlen und den Text zu teilen? Nun, es ist sicherlich eine Option (vielleicht die elegante :)), eine andere könnte Kommentare zum Speichern von Textinformationen verwenden, aber ich frage mich immer noch, ob ich damit programmgesteuert umgehen könnte. jeff vor 7 Jahren 0

2 Antworten auf die Frage

3
Scott Craner

Um die Zahlen zu erhalten, wenn sie am Anfang der Zeichenfolge stehen, können wir Folgendes verwenden:

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))),1))

Als Basisformel findet dies das Ende der Zahl und gibt dieses als Ende der MID () - Funktion zurück.

Hier ist viel los:

SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))Wenn dieser Teil die Zahlen durchläuft, ersetzt er die letzte Instanz jeder Zahl durch }}}.

Das dritte Kriterium von SUBSTITUTE ist die Instanz. Wir finden die Anzahl der Instanzen mit LEN(A2)-LEN(SUBSTITUTE(A2,,"")). Es iteriert durch die Zahlen und ersetzt jede zu einer Zeit durch nichts. Es findet dann den Längenunterschied der ursprünglichen und der neuen Zeichenfolge.

Im Falle von A2 also, wenn es zu iteriert, 2findet es 2 und der äußere Ersatz ersetzt den letzten durch }}}. Dies ist nur ein temporärer Platzhalter.

Die Aggregatfunktion ist eine Multifunktionsfunktion. Die 14 zeigt die Large()Funktionen an, mit denen wir die Funktion verwenden. Das 6sagt die Funktion Fehler zu ignorieren. Dies ist insofern wichtig, als viele der Iterationen nichts finden und einen Fehler zurückgeben.

Mit dem 1am Ende teilt es der Funktion mit, dass wir die höchste Rendite von der Suchfunktion erhalten möchten, die nach den temporären sucht }}}, die durch die Iteration in der letzten Instanz jeder Zahl platziert werden.

Das Aggregat gibt also die gefundene Höchstzahl zurück. Was wir in der Mid-Funktion an das Längenkriterium übergeben.

Wir haben also die Nummer vor der Zeichenkette gefunden.

Wir können also zwei davon multiplizieren, um die gewünschte Ausgabe zu erhalten (Jede mathematische Funktion wandelt den zurückgegebenen String in eine Zahl um):

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,"")))),1))

enter image description here

Eine Einschränkung Die Aggregatfunktion wurde in Excel 2010 eingeführt. Sie funktioniert möglicherweise nicht mit älteren Versionen.

Wenn Sie eine ältere Version haben, müssen Sie diese längere Formel verwenden:

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,""))))),SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,""))))),SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,"")))))))

Es macht ungefähr dasselbe wie das oben Gesagte, dass es zuerst auf Fehler prüfen muss, bevor die max.

Können Sie eine kurze Erklärung hinzufügen, wie dies funktioniert? fixer1234 vor 7 Jahren 0
@ fixer1234 behoben. Siehe Bearbeiten. Scott Craner vor 7 Jahren 0
Entschuldigung, ich habe nach einer Erklärung gefragt. Dies ließ mein Gehirn explodieren. :-) Aber es sieht so aus, als ob es funktioniert, also +1. fixer1234 vor 7 Jahren 0
@ fixer1234 froh, dass du das gemacht hast, es hat mir klar gemacht, dass mein erster Versuch fehlerhaft war und eine Nacharbeit brauchte. Scott Craner vor 7 Jahren 0
@ fixer1234 wenn du denkst das man kompliziert war siehe hier: http://stackoverflow.com/questions/37870970/adjust-project-hours-based-on-priority/37903416#37903416 ein Buch. Scott Craner vor 7 Jahren 0
1
fixer1234

Angenommen, die Zahl ist immer durch ein Leerzeichen von jedem Text getrennt, könnten Sie so etwas verwenden. Angenommen, Ihr Beispiel hat Daten, die mit A2 beginnen, sodass Ihr erstes Ergebnis in C2 normalerweise so aussehen würde:

=A2*B2

Wir müssen die einfachen Zellverweise durch eine Formel ersetzen, die die Quellzelle als Zahl behandelt, wenn es sich nur um eine Zahl handelt, und die Zahl extrahiert, wenn auch Text vorhanden ist. So würde C2 werden:

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

Wenn die Quellzelle keine Zahl ist, findet sie das erste Leerzeichen und gibt die Zeichen davor zurück. Technisch wird ein Textwert zurückgegeben, der Ziffern enthält. Excel ist jedoch intelligent genug, um es bei der Berechnung des Produkts als Zahl zu behandeln (es ist nicht erforderlich, diese in einer anderen Funktion zu verschachteln, um sie in eine Zahl zu konvertieren). Und beachten Sie, dass diese Zahl weiterhin funktioniert, wenn es sich um eine Fließkommazahl handelt.

Vorsichtsmaßnahme: Dies beruht auf "sauberen" Quelldaten, dh entweder einem numerischen Eintrag oder einem Texteintrag, bei dem er mit einer Zahl beginnt, auf die ein Leerzeichen folgt. Wenn es eine Zelle gibt, die dem nicht entspricht, müssen Sie entweder die Daten bereinigen oder eine Form der Fehlerprüfung einschließen. Die beste Lösung wäre, die beiden Datenspalten so aufzuteilen, dass sich Text in einer separaten Zelle befindet.

Das ist großartig, danke! Das erste nicht numerische Zeichen ist jedoch möglicherweise kein Leerzeichen. Ich frage mich, ob wir in der FIND-Funktion irgendeine Mustersuche verwenden können. jeff vor 7 Jahren 0
Was für Charaktere, außer einem Leerzeichen, könnte es sein? fixer1234 vor 7 Jahren 0
Nun, ich möchte keine Whitelist definieren, also suche ich nach einer wilden Suche, die nur Zahlen (und möglicherweise `.` und`, `) ausschließt. jeff vor 7 Jahren 0
Das wird sicher komplizierter als A2 * B2. :-) Ich muss über eine Möglichkeit nachdenken, Regex einzubauen. Wenn zu viele Daten vorhanden sind, um manuell bereinigt zu werden, möchten Sie den Prozess trotzdem automatisieren, um die Nicht-Zahlen in eine andere Zelle zu unterteilen. Wenn Sie das Arbeitsblatt jemals jemandem erklären oder später bearbeiten müssen, ist A2 * B2 viel einfacher, als eine lange Formel zu verstehen, die nur für die Verarbeitung der Zellwerte gilt. fixer1234 vor 7 Jahren 0

Verwandte Probleme