Bendroji formulė
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Santrauka
Norėdami pašalinti skaitinius simbolius iš teksto eilutės, galite naudoti formulę, pagrįstą funkcija TEXTJOIN. Parodytame pavyzdyje formulė C5 yra:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Paaiškinimas
„Excel“ neturi būdo, kaip teksto eilutėje esančias raides mesti į masyvą tiesiai į formulę. Norėdami išspręsti problemą, ši formulė naudoja funkciją MID, naudodamiesi funkcijomis ROW ir INDIRECT, kad pasiektumėte tą patį rezultatą. C5 formulė, nukopijuota žemyn, yra tokia:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Tai atrodo gana sudėtinga, tačiau esmė ta, kad mes sukuriame visų B5 simbolių masyvą ir išbandome kiekvieną simbolį, kad įsitikintume, jog tai skaičius. Jei taip, mes atmetame vertę ir pakeičiame ją tuščia eilute (""). Jei ne, prie „apdoroto“ masyvo pridedame ne skaitmeninį simbolį. Galiausiai mes naudojame funkciją TEXTJOIN (naują „Excel 2019“), kad sujungtume visus simbolius, nepaisydami tuščių reikšmių.
Dirbant iš vidaus, MID funkcija naudojama tekstui išgauti B5, po vieną simbolį. Čia yra ROW ir NETIESIOGINIS fragmentas:
ROW(INDIRECT("1:100"))
sukasi masyvą, kuriame yra 100 tokių skaičių:
(1,2,3,4,5,6,7,8… .99,100)
Pastaba: 100 reiškia maksimalų apdorojamų simbolių skaičių. Pakeiskite, kad atitiktų jūsų duomenis, arba naudokite LEN funkciją, kaip paaiškinta toliau.
Šis masyvas patenka į funkciją MID kaip argumentas start_num . Dėl NUM_CHARS , mes naudojame 1 d.
Funkcija MID pateikia tokį masyvą:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Pastaba: papildomi masyvo elementai pašalinti dėl skaitomumo.
Prie šio masyvo pridedame nulį. Tai yra paprastas triukas, priverčiantis „Excel“ priversti tekstą naudoti skaičių. Skaitmeninės teksto vertės, pvz., „1“, „2“, „3“, „4“ ir kt., Yra konvertuojamos be klaidų, tačiau ne skaitmeninės vertės nepavyks ir bus sukurta klaida #VALUE. Šioms klaidoms užfiksuoti naudojame IF funkciją su ISERR funkcija. Kai pamatome klaidą, žinome, kad turime ne skaitmeninį simbolį, todėl mes įtraukiame šį simbolį į apdorotą masyvą su kita MID funkcija:
MID(B5,ROW(INDIRECT("1:100")),1)
Jei negaunate klaidos, žinome, kad turime skaičių, todėl į masyvą vietoj numerio įterpiame tuščią eilutę ("").
Galutinis masyvo rezultatas patenka į funkciją TEXTJOIN kaip argumentą text1. Norint atskirti, mes naudojame tuščią eilutę (""), o ignore_empty pateikiame TRUE. Tada „TEXTJOIN“ sujungia visas netuščias masyvo reikšmes ir pateikia rezultatą.
Tikslus masyvo ilgis
Užuot užfiksavę tokį skaičių kaip 100 į NETIESIOGINĮ, galite naudoti funkciją LEN, kad sukurtumėte masyvą su realiu simbolių skaičiumi ląstelėje:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN pateikia langelyje esančių simbolių skaičių kaip skaičių, kuris naudojamas vietoj 100. Tai leidžia formulę automatiškai padidinti iki bet kokio simbolių skaičiaus.
Pašalinama papildoma vieta
Kai ištrinate skaitmeninius simbolius, gali likti papildomų tarpų. Norėdami išardyti priekinius ir galinius tarpus bei normalizuoti tarpus tarp žodžių, šiame puslapyje rodomą formulę galite apvynioti TRIM funkcijos viduje:
=TRIM(formula)
Su SEQ
„Excel 365“ naujoji SEQUENCE funkcija gali pakeisti aukščiau esantį ROW + INDIRECT kodą:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Čia mes naudojame SEQUENCE + LEN, kad sukurtume tinkamo ilgio masyvą vienu žingsniu.
Su LET
Šią formulę galime dar labiau supaprastinti naudodamiesi funkcija LET. Kadangi masyvas yra sukurtas du kartus aukščiau su SEQUENCE ir LEN, mes galime apibrėžti masyvą kaip kintamąjį ir sukurti jį tik vieną kartą:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Čia masyvo reikšmė nustatoma tik vieną kartą, tada naudojama du kartus MID funkcijos viduje.
