Generieke formule
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Samenvatting
Om tekst en cijfers te scheiden, kunt u een formule gebruiken op basis van de FIND-functie, de MIN-functie en de LEN-functie met de LINKS- of RECHTS-functie, afhankelijk van of u de tekst of het getal wilt extraheren. In het getoonde voorbeeld is de formule in C5:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
wat 7 teruggeeft, de positie van het getal 3 in de string "appels30".
Uitleg
Overzicht
De formule ziet er ingewikkeld uit, maar de mechanica is in feite vrij eenvoudig.
Zoals bij de meeste formules die tekst splitsen of extraheren, is het belangrijk om de positie te vinden van het ding waarnaar u op zoek bent. Als je eenmaal de positie hebt, kun je andere functies gebruiken om te extraheren wat je nodig hebt.
In dit geval gaan we ervan uit dat cijfers en tekst worden gecombineerd, en dat het nummer achter de tekst staat. Van de originele tekst, die in één cel verschijnt, wil je de tekst en cijfers als volgt in aparte cellen splitsen:
| Origineel | Tekst | Aantal |
| Appels 30 | Appels | 30 |
| perziken 24 | perziken | 24 |
| sinaasappels 12 | sinaasappels | 12 |
| perziken 0 | perziken | 0 |
Zoals hierboven vermeld, is de sleutel in dit geval om de startpositie van het nummer te lokaliseren, wat u kunt doen met een formule als deze:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Als je eenmaal de positie hebt, gebruik je om alleen de tekst te extraheren:
=LEFT(A1,position-1)
En om alleen het nummer te extraheren, gebruikt u:
=RIGHT(A1,LEN(A1)-position+1)
In de eerste formule hierboven gebruiken we de FIND-functie om de startpositie van het nummer te lokaliseren. Voor de zoek_tekst gebruiken we de matrixconstante (0,1,2,3,4,5,6,7,8,9), dit zorgt ervoor dat de FIND-functie een aparte zoekopdracht uitvoert voor elke waarde in de matrixconstante. Omdat de matrixconstante 10 getallen bevat, is het resultaat een matrix met 10 waarden. Als de originele tekst bijvoorbeeld "appels30" is, is de resulterende array:
(8,10,11,7,13,14,15,16,17,18)
Elk getal in deze array vertegenwoordigt de positie van een item in de arrayconstante in de originele tekst.
Vervolgens retourneert de MIN-functie de kleinste waarde in de lijst, die overeenkomt met de positie in van het eerste getal dat in de originele tekst verschijnt. In wezen haalt de functie FIND alle nummerposities op en MIN geeft ons de eerste nummerpositie: merk op dat 7 de kleinste waarde in de array is, die overeenkomt met de positie van het nummer 3 in de originele tekst.
Je vraagt je misschien af wat de vreemde constructie is voor within_text in de find-functie:
B5&"0123456789"
Dit deel van de formule voegt elk mogelijk getal 0-9 samen met de originele tekst in B5. Helaas retourneert FIND geen nul als een waarde niet wordt gevonden, dus dit is gewoon een slimme manier om fouten te voorkomen die kunnen optreden als een getal niet wordt gevonden.
Omdat we in dit voorbeeld aannemen dat het getal altijd als tweede in de originele tekst zal verschijnen, werkt het goed omdat MIN ervoor zorgt dat alleen het kleinste of eerste exemplaar van een getal wordt geretourneerd. Zolang er een getal in de originele tekst voorkomt, wordt die positie teruggegeven.
Als de originele tekst geen getallen bevat, wordt een "nep" -positie gelijk aan de lengte van de originele tekst + 1 geretourneerd. Met deze valse positie zal de LINKS-formule hierboven nog steeds de tekst retourneren en de RECHTER-formule een lege tekenreeks ("").
