Excel

Odstráňte z bunky číselné znaky

Strip Numeric Characters From Cell

Vzorec programu Excel: Odstráňte z bunky číselné znakyGenerický vzorec | _+_ | Zhrnutie

Na odstránenie numerických znakov z textového reťazca môžete použiť vzorec založený na prípone Funkcia TEXTJOIN . V uvedenom príklade vzorec v C5 je:



{= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (A1, ROW ( INDIRECT ('1:100')),1)+0), MID (A1, ROW ( INDIRECT ('1:100')),1),''))}

Poznámka: toto je an maticový vzorec a musí sa zadať pomocou klávesovej skratky + shift + enter, s výnimkou Excel 365 .

Vysvetlenie

Excel nemá spôsob, ako prenášať písmená v textovom reťazci do súboru pole priamo vo vzorci. Ako alternatívne riešenie tento vzorec používa funkciu MID s pomocou funkcií ROW a INDIRECT na dosiahnutie rovnakého výsledku. Skopírovaný vzorec v C5 je:





 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

Vyzerá to dosť komplikovane, ale podstatou je, že vytvoríme súbor pole všetkých znakov v B5 a otestujte každý znak, či je to číslo. Ak je to tak, hodnotu zahodíme a nahradíme znakom prázdny reťazec (''). Ak nie, pridáme do „spracovaného“ poľa nečíselný znak. Nakoniec použijeme funkciu TEXTJOIN (nová v programe Excel 2019) na zreťazenie všetkých znakov dohromady, pričom ignorujeme prázdne hodnoty.

čo znamená #ref v programe Excel

Práca zvnútra von, MID funkcia sa používa na extrahovanie textu v B5, jeden znak naraz.Kľúčom je RIADOK a NEPRIAMY úryvok tu:



 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

čo roztočí pole obsahujúce 100 čísel takto:

{1,2,3,4,5,6,7,8 .... 99,100}

Poznámka: 100 predstavuje maximálny počet znakov na spracovanie. Zmeňte tak, aby vyhovovali vašim údajom, alebo použite funkciu LEN, ako je vysvetlené nižšie.

Toto pole prechádza do funkcie MID ako start_num argument. Pre num_chars , používame 1.

ako triediš v exceli

Funkcia MID vracia pole takto:

 
 ROW ( INDIRECT ('1:100'))

Poznámka: ďalšie položky v poli boli odstránené kvôli čitateľnosti.

Do tohto poľa pridáme nulu. Toto je jednoduchý trik, ktorý núti Excel vynútiť si text na číslo. Číselné textové hodnoty ako „1“, „2“, „3“, „4“ atď. Sa skonvertujú bez chýb, ale nečíselné hodnoty zlyhajú a spôsobia chybu #HODNOTA. Používame IF funkcia s Funkcia ISERR zachytiť tieto chyby. Keď vidíme chybu, vieme, že máme nečíselný znak, a tak tento znak vnesieme do spracovaného pole s ďalšou funkciou MID:

 
{'3''4''6''5''3'' ''J''i''m'' ''M''c''D''o''n''a''l''d'''''''...}

Ak nie zobrazí sa chyba, vieme, že máme číslo, a tak do poľa namiesto čísla vložíme prázdny reťazec ('').

Konečný výsledok poľa prejde do funkcie TEXTJOIN ako text1 argument. Pre oddeľovač , používame prázdny reťazec ('') a pre ignore_empty dodávame TRUE. Pripojte sa teda zreťazuje všetky neprázdne hodnoty v poli a vráti výsledok.

Presná dĺžka poľa

Namiesto pevného zakódovania čísla ako 100 do INDIRECT môžete použiť LEN function takto zostavíme pole so skutočným počtom znakov v bunke:

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

LEN vracia počet znakov v bunke ako číslo, ktoré sa používa namiesto 100. Vďaka tomu môže vzorec automaticky škálovať na ľubovoľný počet znakov.

Odstraňuje sa ďalší priestor

Keď odstránite číselné znaky, môžu vám zostať medzery. Ak chcete odstrániť úvodné a koncové medzery a normalizovať medzery medzi slovami, môžete vzorec zobrazený na tejto stránke zabaliť do súboru Funkcia TRIM :

ako vyplniť každý druhý riadok v programe Excel
 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

S SEKVENCIOU

V Excel 365 , nové Funkcia SEQUENCE môže nahradiť vyššie uvedený ROW + NEPRIAMY kód:

 
= TRIM (formula)

Tu používame SEQUENCE + LEN na zostavenie poľa správnej dĺžky v jednom kroku.

S LET

Tento vzorec môžeme ďalej zefektívniť pomocou LET funkcia . Pretože je pole vytvorené dvakrát vyššie pomocou SEQUENCE a LEN, môžeme pole definovať ako premennú a vytvoriť ho iba raz:

 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, SEQUENCE ( LEN (B5)),1)+0), MID (B5, SEQUENCE ( LEN (B5)),1),''))

Tu je hodnota pole sa nastaví iba raz, potom sa dvakrát použije vo funkcii MID.

Autor Dave Bruns


^