Excel

Opdel tekst og tal

Split Text Numbers

Excel-formel: Opdel tekst og talGenerisk formel
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))
Resumé

For at adskille tekst og tal kan du bruge en formel baseret på FIND-funktion , det MIN-funktion , og LEN-funktion med VENSTRE eller RET funktion afhængigt af om du vil udtrække teksten eller nummeret. I det viste eksempel er formlen i C5:

 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},B5&'0123456789'))

der returnerer 7, placeringen af ​​tallet 3 i strengen 'æbler30'.



Forklaring

Oversigt

Formlen ser kompleks ud, men mekanikken er faktisk ret enkel.



Som med de fleste formler, der deler eller udtrækker tekst, er nøglen at finde position af den ting, du leder efter. Når du har stillingen, kan du bruge andre funktioner til at udtrække det, du har brug for.

I dette tilfælde antager vi, at tal og tekst kombineres, og at tallet vises efter teksten. Fra den originale tekst, der vises i en celle, vil du opdele teksten og numrene i separate celler som denne:



Original Tekst Nummer
Æbler30 Æbler 30
ferskner24 ferskner 24
appelsiner12 appelsiner 12
ferskner0 ferskner 0

Som nævnt ovenfor er nøglen i dette tilfælde at finde startpositionen for nummeret, hvilket du kan gøre med en formel som denne:

excel, hvis cellen indeholder tekst, så vis tekst
 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

Når du har positionen, skal du bruge for at udtrække kun teksten:

 
= LEFT (A1,position-1)

Og for kun at udtrække antallet skal du bruge:



 
= RIGHT (A1, LEN (A1)-position+1)

I den første formel ovenfor bruger vi FIND-funktionen til at finde startpositionen for nummeret. For find_text bruger vi arraykonstanten {0,1,2,3,4,5,6,7,8,9}, hvilket får FIND-funktionen til at udføre en separat søgning efter hver værdi i arraykonstanten. Da matrixkonstanten indeholder 10 tal, bliver resultatet en matrix med 10 værdier. For eksempel, hvis originalteksten er 'apples30' vil den resulterende matrix være:

 
{8,10,11,7,13,14,15,16,17,18}

Hvert tal i denne matrix repræsenterer placeringen af ​​et element i arraykonstanten inden i den originale tekst.

Derefter returnerer MIN-funktionen den mindste værdi på listen, der svarer til positionen i første nummer der vises i den originale tekst. I bund og grund får FIND-funktionen alle nummerpositioner, og MIN giver os den første nummerposition: bemærk, at 7 er den mindste værdi i arrayet, hvilket svarer til positionen for nummer 3 i originalteksten.

Du undrer dig måske over den ulige konstruktion til inden for_tekst i find-funktionen:

hvordan man laver en simpel linjegraf i Excel
 
B5&'0123456789'

Denne del af formlen sammenkæder hvert mulige tal 0-9 med den originale tekst i B5. Desværre returnerer FIND ikke nul, når en værdi ikke findes, så dette er bare en smart måde at undgå fejl, der kan opstå, når et tal ikke findes.

I dette eksempel, da vi antager, at antallet altid vises sekund i originalteksten fungerer det godt, fordi MIN kun tvinger den mindste, eller først forekomst af et nummer, der skal returneres. Så længe et tal gør vises i originalteksten, returneres denne position.

Hvis originalteksten ikke indeholder nogen tal, returneres en 'falsk' position svarende til længden på den originale tekst + 1. I denne falske position returnerer VENSTRE formel stadig teksten, og HØJRE formel returnerer en tom streng ('').

Forfatter Dave Bruns


^