Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka numerot poistetaan Excelin ja Google Sheetsin solun tekstistä.
Keskustelemme kahdesta eri kaavasta numeroiden poistamiseksi tekstistä Excelissä.
SUBSTITUTE -funktion kaava
Voimme käyttää kaavaa, joka perustuu SUBSTITUTE -funktioon. Se on pitkä kaava, mutta se on yksi helpoimmista tavoista poistaa numeroita aakkosnumeerisesta merkkijonosta.
Tässä kaavassa olemme sijoittaneet SUBSTITUTE -funktioita 10 kertaa, kuten tämä:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE) "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Array TEXTJOIN -kaava
Voit poistaa numeroita aakkosnumeerisista merkkijonoista käyttämällä myös monimutkaista matriisikaavaa, joka koostuu funktioista TEXTJOIN, MID, ROW ja INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (Epäsuora ("1:" & LEN (B3)))), 1) +0), KESKI (B3, Rivi (Epäsuora ("1:") & LEN (B3))), 1), ""))} |
Huomautus: TEXTJOIN on uusi Excel -toiminto, joka on saatavana Excel 2022+: ssa ja Office 365: ssä.
Tämä on monimutkainen kaava, joten jaamme sen vaiheisiin ymmärtääksemme sen paremmin.
Vaihe 1
MID -funktiota käytetään aakkosnumeerisen merkkijonon purkamiseen argumenttien start_num ja num_chars perusteella.
MID -funktion start_num -argumentissa käytämme tuloksena olevaa taulukkoluetteloa ROW- ja INDIRECT -funktioista.
| 1 | = RIVI (Epäsuora ("1:" & LEN (B3))) |
Ja numeromerkit-argumentille asetamme 1. Kun argumentit on asetettu MID-funktioon, se palauttaa taulukon.
| 1 | {= KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3))), 1)} |
Vaihe 2
Lisäämme nollaan tuloksena olevan taulukon jokaiseen arvoon (jonka saamme yllä olevasta MID -funktiosta). Jos lisäät Excelissä numeroita ei-numeerisiin merkkeihin, saamme #ARVO! Virhe. Joten, kun olet lisännyt 0 yllä olevaan taulukkoon, saamme joukon numeroita ja #Arvo! Virheet.
| 1 | {= KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3)))), 1) +0} |
Vaihe 3
0: n lisäämisen jälkeen tuloksena oleva taulukko laitetaan ISERR -funktioon. Kuten tiedämme, ISERR-funktio palauttaa TOSI virheille ja EPÄTOSI virheellisille arvoille.
Joten se antaa taulukon TOSI ja EPÄTOSI, TOSI ei-numeerisille merkeille ja EPÄTOSI numeroille.
| 1 | = ISERR (KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3)))), 1) +0) |
Vaihe 4
Lisäämme nyt IF -toiminnon.
IF -toiminto tarkistaa ISERR -toiminnon tuloksen (vaihe 3). Jos sen arvo on TOSI, se palauttaa taulukon kaikista aakkosnumeerisen merkkijonon merkeistä. Tätä varten olemme lisänneet toisen MID -toiminnon lisäämättä nollaa loppuun. Jos IF -funktion arvo on EPÄTOSI, se palauttaa tyhjän (“”).
Tällä tavalla meillä on taulukko, joka sisältää vain merkkijonon muita kuin numeerisia merkkejä.
| 1 | = JOS (ISERR (KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3))))))), 1) +0), KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3)))), 1 ), "") |
Vaihe 5
Lopuksi yllä oleva taulukko lisätään TEXTJOIN -funktioon. TEXTJOIN -toiminto yhdistää kaikki yllä olevan taulukon merkit ja jättää tyhjän merkkijonon huomiotta.
Tämän funktion erotin on tyhjä merkkijono (“”) ja ignore_empty -argumentin arvo syötetään TOSI.
Tämä antaa meille halutun tuloksen eli vain aakkosnumeerisen merkkijonon ei-numeeriset merkit.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (Epäsuora ("1:" & LEN (B3)))), 1) +0), KESKI (B3, Rivi (Epäsuora ("1:") & LEN (B3))), 1), ""))} |
Huomautus: Tämä on Array -kaava. Kun syötät taulukkokaavoja Excel 2022: een tai aiempaan, sinun on käytettävä CTRL + VAIHTO + ENTER kaavan syöttämiseksi tavallisen sijaan TULLA SISÄÄN.
Tiedät syöttäneesi kaavan oikein näkyvillä olevilla hakasulkeilla. ÄLÄ kirjoita kiharoita manuaalisesti, kaava ei toimi.
Office 365: n (ja oletettavasti Excel -versioiden vuoden 2022 jälkeen) avulla voit kirjoittaa kaavan tavalliseen tapaan.
TRIM -toiminto
Kun numerot poistetaan merkkijonosta, saatamme jättää ylimääräisiä välilyöntejä. Jos haluat poistaa kaikki loppu- ja etumerkit sekä sanojen väliset ylimääräiset välilyönnit, voimme käyttää TRIM -funktiota ennen pääkaavaa seuraavasti:
| 1 | = TRIM (C3) |
Poista numerot Google Sheetsin tekstistä
Kaava poistaa numerot tekstistä toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
