Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā noņemt skaitļus no teksta šūnā programmā Excel un Google izklājlapas.
Mēs apspriedīsim divas dažādas formulas skaitļu noņemšanai no teksta programmā Excel.
FUNKCIJAS formula
Mēs varam izmantot formulu, kuras pamatā ir funkcija SUBSTITUTE. Tā ir gara formula, taču tas ir viens no vienkāršākajiem veidiem, kā noņemt ciparus no burtciparu virknes.
Šajā formulā mēs esam 10 reizes ievietojuši SUBSTITUTE funkcijas, piemēram:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (B3,1, "))))), 2," "), 3," "), 4," "," "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Masīva TEXTJOIN formula
Lai noņemtu ciparus no burtciparu virknēm, mēs varam izmantot arī sarežģītu masīvu formulu, kas sastāv no funkcijām TEXTJOIN, MID, ROW un INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (NETIEŠS ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (NETIEŠS ("1:") & LEN (B3))), 1), ""))} |
Piezīme. TEXTJOIN ir jauna Excel funkcija, kas pieejama programmā Excel 2022+ un Office 365.
Šī ir sarežģīta formula, tāpēc mēs to sadalīsim posmos, lai to labāk izprastu.
1. darbība
Funkcija MID tiek izmantota burtciparu virknes iegūšanai, pamatojoties uz argumentiem start_num un num_chars.
Funkcijas MID argumentam start_num mēs izmantosim iegūto masīvu sarakstu no funkcijām ROW un INDIRECT.
| 1 | = Rinda (netieša ("1:" & LEN (B3))) |
Un argumentam num-chars mēs ieliksim 1. Pēc argumentu ievietošanas funkcijā MID tas atgriezīs masīvu.
| 1 | {= MID (B3, ROW (NETIEŠS ("1:" & LEN (B3))), 1)} |
2. solis
Mēs pievienosim nulli katrai rezultāta masīva vērtībai (ko mēs iegūstam no iepriekš minētās MID funkcijas). Ja programmā Excel pievienojat ciparus neskaitliskām rakstzīmēm, mēs iegūsim #VALUE! Kļūda. Tātad, pēc 0 pievienošanas iepriekš minētajā masīvā, mēs iegūsim skaitļu masīvu un #Vērtība! Kļūdas.
| 1 | {= MID (B3, ROW (NETIEŠS ("1:" & LEN (B3)))), 1) +0} |
3. solis
Pēc 0 pievienošanas iegūtais masīvs tiek ievietots ISERR funkcijā. Kā mēs zinām, funkcija ISERR atgriež TRUE kļūdām un FALSE vērtībām, kas nav kļūdas.
Tātad, tas sniegs masīvu TRUE un FALSE, TRUE, ja nav ciparu rakstzīmes, un FALSE, ja skaitļi.
| 1 | = ISERR (MID (B3, ROW (NETIEŠS ("1:" & LEN (B3)))), 1) +0) |
4. solis
Tagad mēs pievienosim IF funkciju.
IF funkcija pārbaudīs ISERR funkcijas rezultātu (3. darbība). Ja tā vērtība ir TRUE, tā atgriezīs visu burtciparu virknes rakstzīmju masīvu. Šim nolūkam mēs esam pievienojuši vēl vienu MID funkciju, beigās nepievienojot nulli. Ja funkcijas IF vērtība ir FALSE, tā atgriezīsies tukša (“”).
Tādā veidā mums būs masīvs, kurā būs tikai virknes neskaitliskās rakstzīmes.
| 1 | = JA (ISERR (MID (B3, ROW (NETIEŠS ("1:" & LEN (B3))))), 1) +0), MID (B3, ROW (NETIEŠS ("1:" & LEN (B3)))), 1 ), "") |
5. solis
Visbeidzot, iepriekš minētais masīvs tiek ievietots funkcijā TEXTJOIN. Funkcija TEXTJOIN apvienos visas iepriekš minētā masīva rakstzīmes un ignorēs tukšo virkni.
Šīs funkcijas norobežotājam tiek iestatīta tukša virkne (“”), un argumenta ignore_empty vērtība tiek ievadīta TRUE.
Tas dos mums vēlamo rezultātu, t.i., tikai burtciparu virknes neskaitliskās rakstzīmes.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (NETIEŠS ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (NETIEŠS ("1:") & LEN (B3))), 1), ""))} |
Piezīme. Šī ir masīva formula. Ievadot masīva formulas programmā Excel 2022 vai vecākā versijā, jāizmanto CTRL + SHIFT + ENTER lai ievadītu formulu, nevis parasto ENTER.
Jūs zināt, ka esat pareizi ievadījis formulu, izmantojot parādītās cirtainās iekavas. NEDRĪKST manuāli ierakstīt cirtainās iekavas, formula nedarbosies.
Izmantojot Office 365 (un, iespējams, Excel versijas pēc 2022. gada), varat vienkārši ievadīt formulu kā parasti.
TRIM funkcija
Kad skaitļi tiek noņemti no virknes, mums var būt palikušas papildu atstarpes. Lai noņemtu visas pēdējās un priekšējās atstarpes un papildu atstarpes starp vārdiem, mēs varam izmantot funkciju TRIM pirms galvenās formulas, piemēram:
| 1 | = TRIM (C3) |
Noņemt skaitļus no teksta Google izklājlapās
Formula ciparu noņemšanai no teksta Google izklājlapās darbojas tieši tāpat kā programmā Excel:
