Üldine valem
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Kokkuvõte
Numbrimärkide eemaldamiseks tekstistringist saate kasutada funktsiooni TEXTJOIN põhinevat valemit. Näidatud näites on valem C5-s:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Selgitus
Excelil pole võimalust tekstistringi tähti valemisse otse massiivi valada. Lahendusena kasutab see valem funktsiooni MID, sama tulemuse saavutamiseks funktsioonide ROW ja INDIRECT abiga. Kopeeritud C5 valem on järgmine:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
See tundub üsna keeruline, kuid põhiline on see, et loome massiivi kõikidest tähemärkidest B5 ja testime iga märki, kas see on number. Kui jah, siis viskame väärtuse kõrvale ja asendame selle tühja stringiga (""). Kui ei, lisame mittetöötava märgi "töödeldud" massiivi. Lõpuks kasutame funktsiooni TEXTJOIN (uus rakenduses Excel 2019) kõigi tähemärkide liitmiseks, ignoreerides tühje väärtusi.
Seestpoolt välja töötades kasutatakse funktsiooni MID, et eraldada tekst B5-st üks tähemärk korraga. Võti on siin RID ja INDIRECT koodilõik:
ROW(INDIRECT("1:100"))
mis keerutab üles massiivi, mis sisaldab 100 sellist numbrit:
(1,2,3,4,5,6,7,8…, 99,100)
Märkus: 100 tähistab maksimaalselt tähemärke töödeldavaks. Muutke oma andmetele vastavaks või kasutage funktsiooni LEN vastavalt allpool kirjeldatule.
See massiiv läheb funktsiooni MID kui argument algus_arv . Sest märkide_arv , me kasutame 1.
Funktsioon MID tagastab sellise massiivi:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Märkus: massiivist eemaldati loetavuse huvides täiendavad üksused.
Sellele massiivile lisame nulli. See on lihtne trikk, mis sunnib Exceli sundima teksti numbrile. Numbrilised tekstiväärtused, nagu "1", "2", "3", "4" jne, teisendatakse ilma vigadeta, kuid mittearvulised väärtused ebaõnnestuvad ja viskavad tõrke #VALUE. Nende vigade tabamiseks kasutame funktsiooni IS koos funktsiooniga ISERR. Kui näeme viga, teame, et meil on mitte numbriline märk, nii et toome selle märgi töödeldud massiivi teise MID-funktsiooniga:
MID(B5,ROW(INDIRECT("1:100")),1)
Kui viga ei õnnestu, teame, et meil on number, nii et sisestame massiivi numbri asemel tühja stringi ("").
Lõplik massiivi tulemus läheb funktsiooni TEXTJOIN argumendina text1. Piiraja jaoks kasutame tühja stringi ("") ja ignore_empty jaoks sisestame TRUE. Seejärel liidab TEXTJOIN kõik massiivi mittetühjad väärtused ja tagastab tulemuse.
Massiivi täpne pikkus
Selle asemel, et kodeerida arv, näiteks 100 INDIRECT-i, saate funktsiooni LEN abil rakendada massiivi lahtris tegeliku tähemärkide arvuga:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN tagastab lahtris olevate märkide arvu arvuna, mida kasutatakse 100 asemel. See võimaldab valemil automaatselt laiendada suvalist arvu tähemärke.
Lisaruumi eemaldamine
Numbriliste tähemärkide eemaldamisel võib teil jääda lisatühikuid. Esi- ja tagantjärgi tühjendamiseks ning sõnade vaheliste tühikute normaliseerimiseks võite sellel lehel näidatud valemi mähkida funktsiooni TRIM sisse:
=TRIM(formula)
Järjestusega
Rakenduses Excel 365 saab uus funktsioon SEQUENCE asendada ülaltoodud RID + INDIRECT koodi:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Siin kasutame SEQUENCE + LEN, et ehitada ühe sammuga õige pikkusega massiiv.
Koos LET-iga
Saame seda valemit veelgi lihtsustada funktsiooniga LET. Kuna massiiv luuakse ülal kaks korda järjestuste ja järjestuste abil, saame massiivi määratleda muutujana ja luua selle vaid üks kord:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Siin seatakse massiivi väärtus vaid üks kord, seejärel kasutatakse funktsiooni MID sees kaks korda.
