Excel

Poista numeromerkit solusta

Strip Numeric Characters From Cell

Excel -kaava: Poista numeeriset merkit solustaYleinen kaava | _+_ | Yhteenveto

Voit poistaa numeerisia merkkejä tekstimerkkijonosta käyttämällä kaavaan perustuvaa kaavaa TEXTJOIN -toiminto . Esitetyssä esimerkissä C5: n kaava on:





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

Huomaa: tämä on taulukon kaava ja on syötettävä näppäimillä control + shift + enter, paitsi sisään Excel 365 .

Selitys

Excelillä ei ole tapaa siirtää tekstimerkkijonon kirjaimia matriisi suoraan kaavassa. Voit kiertää tämän kaavan käyttämällä MID -toimintoa ROW- ja INDIRECT -toimintojen avulla saman tuloksen saavuttamiseksi. C5: n kaava kopioituina on:





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

Tämä näyttää melko monimutkaiselta, mutta ydin on, että luomme matriisi kaikista B5 -merkeistä ja testaa jokainen merkki nähdäksesi, onko se numero. Jos näin on, hylkäämme arvon ja korvaamme sen merkillä tyhjä merkkijono (''). Jos ei, lisäämme ei-numeerisen merkin käsiteltyyn taulukkoon. Lopuksi käytämme TEXTJOIN -funktiota (uusi Excel 2019: ssä) kaikkien merkkien yhdistämiseksi, jättäen tyhjät arvot huomiotta.

joukkolainan nykyarvo on erinomainen

Toimii sisältä ulospäin, MID -toiminto käytetään poimimaan B5: n teksti yksi merkki kerrallaan.Avain on RIVI ja Epäsuora katkelma täältä:



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

joka muodostaa 100 numeroa sisältävän taulukon seuraavasti:

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

Huomautus: 100 edustaa käsiteltävien merkkien enimmäismäärää. Muuta tietojasi sopivaksi tai käytä LEN -toimintoa alla kuvatulla tavalla.

Tämä taulukko siirtyy MID -toimintoon nimellä aloitusnumero Perustelu. Varten lukumäärä , käytämme 1.

jos funktio excel kyllä ​​tai ei

MID -funktio palauttaa seuraavanlaisen taulukon:

 
 ROW ( INDIRECT ('1:100'))

Huomautus: taulukon ylimääräiset kohteet poistettiin luettavuuden vuoksi.

Tähän taulukkoon lisätään nolla. Tämä on yksinkertainen temppu, joka pakottaa Excelin pakottamaan tekstin numeroon. Numeeriset tekstiarvot, kuten '1', '2', '3', '4' jne. Muunnetaan ilman virheitä, mutta ei-numeeriset arvot epäonnistuvat ja aiheuttavat #VALUE-virheen. Käytämme IF -toiminto kanssa ISERR -toiminto tarttumaan näihin virheisiin. Kun näemme virheen, tiedämme, että meillä on ei-numeerinen merkki, joten tuomme merkin käsitellyksi matriisi toisen MID -toiminnon kanssa:

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

Jos älä saat virheen, tiedämme, että meillä on numero, joten lisäämme tyhjän merkkijonon ('') taulukkoon numeron sijasta.

Matriisin lopullinen tulos siirtyy TEXTJOIN -funktioon nimellä teksti 1 Perustelu. Varten erotin , käytämme tyhjää merkkijonoa ('') ja ignore_empty toimitamme TOSI. TEXTJOIN sitten ketjutuksia kaikki ei-tyhjät arvot taulukossa ja palauttaa tuloksen.

Tarkka matriisin pituus

Voit käyttää numeron 100 kaltaista koodia INDIRECT -koodin sijaan LEN -toiminto rakentaa taulukko, jossa on solun todellinen merkkimäärä, näin:

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

LEN palauttaa solun merkkien määrän numeroina, joita käytetään 100: n sijasta. Näin kaava skaalautuu automaattisesti mihin tahansa merkkiin.

Lisätilan poistaminen

Kun poistat numeerisia merkkejä, saatat jäädä ylimääräisiä välilyöntejä. Jos haluat poistaa välilyönnit eteen ja taakse ja normalisoida sanojen väliset välilyönnit, voit kääriä tällä sivulla esitetyn kaavan TRIM -toiminto :

kuinka määritellä tulostetut otsikot Excel 2013: ssa
 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

SEQUENCE -toiminnolla

Sisään Excel 365 , uusi SEQUENCE -toiminto voi korvata yllä olevan ROW + INDIRECT -koodin:

 
= TRIM (formula)

Tässä käytämme SEQUENCE + LEN -ohjelmaa oikean pituisen taulukon rakentamiseen yhdessä vaiheessa.

LET: llä

Voimme virtaviivaistaa tätä kaavaa edelleen LET -toiminto . Koska matriisi luodaan kahdesti yllä sekvenssillä ja LEN: llä, voimme määritellä taulukon muuttujaksi ja luoda sen vain kerran:

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

Tässä arvo matriisi asetetaan vain kerran ja käytetään sitten kahdesti MID -toiminnon sisällä.

Kirjailija Dave Bruns


^