Excel

Rozdeľte text a čísla

Split Text Numbers

Vzorec programu Excel: Rozdeľte text a číslaGenerický vzorec | _+_ | Zhrnutie

Na oddelenie textu a čísiel môžete použiť vzorec založený na FIND (funkcia) , Funkcia MIN , a LEN function s VĽAVO alebo SPRÁVNY podľa toho, či chcete extrahovať text alebo číslo. V uvedenom príklade vzorec v C5 je:



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

čím sa vráti 7, pozícia čísla 3 v reťazci „jablká30“.

Vysvetlenie

Prehľad

Vzorec vyzerá komplexne, ale mechanika je v skutočnosti celkom jednoduchá.





Rovnako ako pre väčšinu vzorcov, ktoré rozdeľujú alebo extrahujú text, je kľúčové nájsť súbor pozíciu veci, ktorú hľadáte. Akonáhle máte pozíciu, môžete pomocou ďalších funkcií extrahovať to, čo potrebujete.

V tomto prípade predpokladáme, že čísla a text sú kombinované a že za textom je číslo. Z pôvodného textu, ktorý sa zobrazuje v jednej bunke, chcete text a čísla rozdeliť do samostatných buniek takto:



Originál Text Číslo
Jablká 30 Jablká 30
broskyne24 broskyne 24
pomaranče 12 pomaranče 12
broskyne0 broskyne 0

Ako je uvedené vyššie, kľúčom v tomto prípade je nájsť počiatočnú pozíciu čísla, čo môžete urobiť pomocou vzorca ako je tento:

Excel vypočítať čas medzi dvoma časmi
 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},B5&'0123456789'))

Keď máte pozíciu, na extrahovanie iba textu použite:

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

A aby ste získali iba číslo, použite:

 
= LEFT (A1,position-1)

V prvom vzorci vyššie používame na nájdenie počiatočnej polohy čísla funkciu NÁJSŤ. Pre find_text používame maticovú konštantu {0,1,2,3,4,5,6,7,8,9}, čo spôsobuje, že funkcia FIND vykoná samostatné vyhľadávanie pre každú hodnotu v konštante poľa. Pretože konštanta poľa obsahuje 10 čísel, výsledkom bude pole s 10 hodnotami. Ak je napríklad pôvodný text „jablká30“, výsledné pole bude vyzerať takto:

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

Každé číslo v tomto poli predstavuje pozíciu položky v konštante poľa v pôvodnom texte.

Ďalej funkcia MIN vráti najmenšiu hodnotu v zozname, ktorá zodpovedá pozícii v prvé číslo ktoré je uvedené v pôvodnom texte. V podstate funkcia FIND získa všetky číselné polohy a MIN nám poskytne prvú číselnú pozíciu: všimnite si, že 7 je najmenšia hodnota v poli, ktorá zodpovedá pozícii čísla 3 v pôvodnom texte.

Možno sa pýtate na zvláštnu konštrukciu pre within_text vo vyhľadávacej funkcii:

ako pridať chybovú lištu v programe Excel
 
{8,10,11,7,13,14,15,16,17,18}

Táto časť vzorca spája všetky možné čísla 0-9 s pôvodným textom v B5. FIND bohužiaľ nevracia nulu, ak sa nenašla hodnota, takže je to len šikovný spôsob, ako sa vyhnúť chybám, ktoré by sa mohli vyskytnúť, ak sa číslo nenájde.

V tomto prípade predpokladáme, že sa číslo vždy zobrazí druhý v pôvodnom texte to funguje dobre, pretože MIN si vynúti iba najmenších, príp najprv výskytu čísla, ktoré sa má vrátiť. Pokiaľ číslo robí sa objaví v pôvodnom texte, táto pozícia sa vráti.

Ak pôvodný text neobsahuje žiadne čísla, vráti sa „falošná“ pozícia rovnajúca sa dĺžke pôvodného textu + 1. Pri tejto falošnej pozícii vzorec VĽAVO vyššie vráti text a PRAVÝ vzorec vráti znak prázdny reťazec ('').

Autor Dave Bruns


^