Yleinen kaava
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Yhteenveto
Voit poistaa numeerisia merkkejä tekstimerkkijonosta käyttämällä TEXTJOIN-funktioon perustuvaa kaavaa. Esitetyssä esimerkissä kaava kohdassa C5 on:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Selitys
Excelillä ei ole tapaa heittää tekstimerkkijonon kirjaimia suoraan kaavaan. Ratkaisuna tämä kaava käyttää MID-toimintoa, ROW- ja INDIRECT-toimintojen avulla saman tuloksen saavuttamiseksi. C5: n kaava kopioituna alaspäin 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 joukon kaikkia merkkejä B5: ssä ja testaamme jokaisen merkin, onko se luku. Jos näin on, hylkäämme arvon ja korvataan se tyhjällä merkkijonolla (""). Jos ei, lisätään ei-numeerinen merkki "käsiteltyyn" taulukkoon. Lopuksi käytämme TEXTJOIN-toimintoa (uusi Excel 2019: ssä) liittämään kaikki merkit yhteen ohittamatta tyhjiä arvoja.
Sisältäpäin työskentelemällä MID-toimintoa käytetään B5: n tekstin purkamiseen, yksi merkki kerrallaan. Avain on RIVI ja EPÄSUORA katkelma tässä:
ROW(INDIRECT("1:100"))
joka pyörittää matriisia, joka sisältää 100 numeroa näin:
(1,2,3,4,5,6,7,8… .99,100)
Huomaa: 100 edustaa käsiteltävien merkkien enimmäismäärää. Muuta tietojen mukaan tai käytä LEN-toimintoa alla kuvatulla tavalla.
Tämä taulukko menee MID-funktioon start_num- argumenttina. Sillä merkkimäärä käytämme 1.
MID-funktio palauttaa seuraavanlaisen taulukon:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Huomaa: ylimääräiset matriisin kohteet poistettiin luettavuuden vuoksi.
Tähän ryhmään lisätään nolla. Tämä on yksinkertainen temppu, joka pakottaa Excelin pakottamaan tekstin numeroksi. Numeeriset tekstiarvot, kuten "1", "2", "3", "4" jne., Muunnetaan ilman virheitä, mutta muut kuin numeeriset arvot epäonnistuvat ja heittävät #ARVO-virheen. Käytämme IF-toimintoa ISERR-toiminnon kanssa näiden virheiden havaitsemiseksi. Kun näemme virheen, tiedämme, että meillä on ei-numeerinen merkki, joten tuomme kyseisen merkin käsiteltyyn taulukoon toisella MID-toiminnolla:
MID(B5,ROW(INDIRECT("1:100")),1)
Jos et saa virhettä, tiedämme, että meillä on numero, joten syötämme matriisiin tyhjän merkkijonon ("") luvun sijasta.
Lopullinen taulukon tulos menee TEXTJOIN-funktioon text1-argumenttina. Erottajana käytämme tyhjää merkkijonoa ("") ja ignore_empty: lle toimitamme TOSI. TEXTJOIN yhdistää sitten kaikki taulukon ei-tyhjät arvot ja palauttaa tuloksen.
Taulukon tarkka pituus
Sen sijaan, että kovakoodaat luvun 100, kuten INDIRECT, voit käyttää LEN-funktiota rakentaaksesi taulukon, jossa solun todellinen merkkimäärä on näin:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN palauttaa solun merkkien määrän numeroina, jota käytetään 100: n sijasta. Tämän avulla kaava voi skaalata automaattisesti mihin tahansa merkkimäärään.
Lisätilan poistaminen
Kun poistat numeromerkkejä, sinulla voi olla ylimääräisiä välilyöntejä. Voit tyhjentää etu- ja loppuvälit ja normalisoida sanojen väliset välilyönnit kiertämällä tällä sivulla esitetyn kaavan TRIM-toiminnon sisään:
=TRIM(formula)
SEQUENCE: llä
Excel 365: ssä uusi SEQUENCE-toiminto voi korvata yllä olevan RIVI + EPÄSUORA-koodin:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Tässä käytämme SEQUENCE + LEN rakentamaan oikean pituisen taulukon yhdessä vaiheessa.
LET: n kanssa
Voimme tehostaa tätä kaavaa edelleen LET-toiminnolla. Koska taulukko on luotu kahdesti yllä sekvenssillä ja LEN, voimme määrittää matriisin muuttujana ja luoda sen vain kerran:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Tässä taulukon arvo asetetaan vain kerran, sitten sitä käytetään kahdesti MID-toiminnon sisällä.
