Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip pašalinti skaičius iš teksto langelyje „Excel“ ir „Google“ skaičiuoklėse.
Mes aptarsime dvi skirtingas skaičių pašalinimo iš teksto „Excel“ formules.
Funkcijos formulė PAKEITIMAS
Mes galime naudoti formulę, pagrįstą funkcija SUBSTITUTE. Tai ilga formulė, tačiau tai yra vienas iš paprasčiausių būdų pašalinti skaičius iš raidinės ir skaitmeninės eilutės.
Šioje formulėje mes 10 kartų įdėjome SUBSTITUTE funkcijas, pavyzdžiui:
| 1 | = PAKAITA (PAKEITIMAS (PAKEITIMAS (PAKEITIS (PAKEITIMAS (PAKEITIMAS (PAKEITIMAS (PAKEITIMAS (PAKEITIS (PAKEITIS (B3,1, "")), 2, ""), 3, "", 4, "", "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Masyvo TEXTJOIN formulė
Norėdami pašalinti skaičius iš raidinių ir skaitinių eilučių, taip pat galime naudoti sudėtingą masyvo formulę, kurią sudaro funkcijos TEXTJOIN, MID, ROW ir INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Pastaba: „TEXTJOIN“ yra nauja „Excel“ funkcija, pasiekiama „Excel 2022+“ ir „Office 365“.
Tai sudėtinga formulė, todėl suskirstysime ją į veiksmus, kad geriau suprastume.
1 žingsnis
Funkcija MID naudojama raidinei ir skaitinei eilutei išgauti remiantis argumentais start_num ir num_chars.
Funkcijos MID argumentui start_num naudosime gautų masyvų sąrašą iš funkcijų ROW ir INDIRECT.
| 1 | = EILUTĖ (NETIESIOGINĖ ("1:" & LEN (B3))) |
O argumentui num-chars įdėsime 1. Įdėję argumentus į funkciją MID, jis grąžins masyvą.
| 1 | {= MID (B3, EILUTĖ (NETIESIOGINĖ („1:“ ir LEN (B3))), 1)} |
2 žingsnis
Pridėsime nulį prie kiekvienos gauto masyvo vertės (kurią gauname iš aukščiau pateiktos MID funkcijos). Jei „Excel“ pridėsite skaičių prie ne skaitinių simbolių, gausime #VALUE! Klaida. Taigi, pridėję 0 aukščiau esančiame masyve, gausime skaičių masyvą ir #Vertybė! Klaidos.
| 1 | {= MID (B3, EILUTĖ (NETIESIOGINĖ („1:“ & LEN (B3)))), 1) +0} |
3 žingsnis
Pridėjus 0, gautas masyvas įtraukiamas į ISERR funkciją. Kaip žinome, funkcija ISERR grąžina TRUE už klaidas ir FALSE, jei nėra klaidų.
Taigi, jis suteiks masyvą TRUE ir FALSE, TRUE neskaitiniams simboliams ir FALSE skaičiams.
| 1 | = ISERR (MID (B3, ROW (NETIESIOGINIS ("1:" & LEN (B3)))), 1) +0) |
4 žingsnis
Dabar pridėsime IF funkciją.
IF funkcija patikrins ISERR funkcijos rezultatus (3 veiksmas). Jei jo reikšmė yra TRUE, bus pateiktas visų raidinių ir skaitinių eilučių simbolių masyvas. Norėdami tai padaryti, pridėjome dar vieną MID funkciją, nepridėdami nulio pabaigoje. Jei funkcijos IF vertė yra FALSE, ji grįš tuščia („“).
Tokiu būdu turėsime masyvą, kuriame yra tik neskaitiniai eilutės simboliai.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
5 žingsnis
Galiausiai aukščiau pateiktas masyvas įtraukiamas į funkciją TEXTJOIN. Funkcija TEXTJOIN sujungs visus aukščiau esančio masyvo simbolius ir ignoruos tuščią eilutę.
Šios funkcijos skiriamuoju ženklu nustatoma tuščia eilutė („“), o argumento ignore_empty reikšmė įvedama TRUE.
Tai duos norimą rezultatą, t. Y. Tik neskaitinius raidžių ir skaitmenų eilutės simbolius.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Pastaba: tai yra masyvo formulė. Įvesdami masyvo formules programoje „Excel 2022“ ar ankstesnėje versijoje, turite naudoti CTRL + SHIFT + ENTER įvesti formulę, o ne įprastą Įveskite.
Jūs žinosite, kad teisingai įvedėte formulę pagal atsirandančius garbanotus skliaustus. NEGALIMA rankiniu būdu įvesti garbanotų skliaustų, formulė neveiks.
Naudodami „Office 365“ (ir, tikėtina, „Excel“ versijas po 2022 m.), Galite tiesiog įvesti formulę kaip įprasta.
TRIM funkcija
Kai skaičiai pašalinami iš eilutės, galime palikti papildomų tarpų. Norėdami pašalinti visas galines ir priekines tarpas bei papildomus tarpus tarp žodžių, prieš pagrindinę formulę galime naudoti funkciją TRIM, pvz .:
| 1 | = TRIM (C3) |
Pašalinkite skaičius iš teksto „Google“ skaičiuoklėse
Skaičių pašalinimo iš teksto formulė „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
