Lejupielādējiet darbgrāmatas piemēru
Šī apmācība būs parādīt, kā atrast un izvilkt skaitli no teksta virknes programmā Excel un Google izklājlapas.
Atrodiet un izvelciet skaitli no virknes
Dažreiz jūsu dati var saturēt ciparus un tekstu, un jūs vēlaties iegūt skaitliskos datus.
Ja skaitļa daļa atrodas virknes labajā vai kreisajā pusē, ir salīdzinoši viegli sadalīt numuru un tekstu. Tomēr, ja skaitļi atrodas virknes iekšpusē, t.i., starp divām teksta virknēm, jums būs jāizmanto daudz sarežģītāka formula (parādīta zemāk).
TEXTJOIN - izvilkt numurus programmā Excel 2016+
Programmā Excel 2016 tika ieviesta funkcija TEXTJOIN, kas var iegūt skaitļus no jebkuras teksta virknes. Šeit ir formula:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (NETIEŠS ("1:" & LEN (B3))), 1)*1), "")) |
Apskatīsim, kā šī formula darbojas.
Funkcijas ROW, INDIRECT un LEN atgriež skaitļu masīvu, kas atbilst katrai jūsu burtciparu virknes rakstzīmei. Mūsu gadījumā vārdam “Monday01Day” ir 11 rakstzīmes, tāpēc funkcija ROW pēc tam atgriezīs masīvu {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Tālāk funkcija MID izraksta katru rakstzīmi no teksta virknes, izveidojot masīvu, kurā ir jūsu sākotnējā virkne:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Reizinot katru masīva vērtību ar 1, tiks izveidots masīvs ar kļūdām. Ja masīva rakstzīme bija teksts:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE) !}), "")) |
Pēc tam funkcija IFERROR noņem kļūdas vērtības:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Atstājot tikai funkciju TEXTJOIN, kas savieno pārējos skaitļus kopā.
Ņemiet vērā, ka formula sniegs jums visas virknes ciparu rakstzīmes. Piemēram, ja jūsu burtciparu virkne ir Monday01Day01, tā rezultātā tiks iegūts 0101.
Izvilkt numurus - pirms Excel 2016
Pirms Excel 2016 jūs varētu izmantot daudz sarežģītāku metodi, lai no teksta iegūtu skaitļus. Jūs varat izmantot FIND funkciju kopā ar funkcijām IFERROR un MIN, lai noteiktu gan skaitļa daļas pirmo pozīciju, gan teksta daļas pirmo pozīciju pēc numura. Tad mēs vienkārši iegūstam skaitļa daļu ar funkciju MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a") , "b", "c", "d", "e", "f", "g", "h", "es", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (MEKLĒT ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (MEKLĒT ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Piezīme. Šī ir masīva formula, tā jāievada, nospiežot CTRL + SHIFT + ENTER, nevis tikai ENTER.
Apskatīsim soli pa solim, kā šī formula darbojas.
Atrodiet dūres numuru
Mēs varam izmantot FIND funkciju, lai atrastu numura sākuma pozīciju.
| 1 | = MIN (IFERROR (MEKLĒT ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Funkcijas FIND argumentam find_text mēs izmantojam masīva konstanti {0,1,2,3,4,5,6,7,8,9}, kas liek funkcijai FIND veikt atsevišķu meklēšanu katrai masīva konstante.
Funkcijas FIND arguments text_text mūsu gadījumā ir Monday01Day, kurā 1 atrodams 8. pozīcijā, bet 0 - 7. pozīcijā, tāpēc mūsu rezultātu masīvs būs šāds: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Izmantojot funkciju IFERROR, mēs #VALUE kļūdas aizstājam ar 999999999. Tad mēs vienkārši meklējam minimumu šajā masīvā un iegūstam pirmā numura vietu (7).
Lūdzu, ņemiet vērā, ka iepriekš minētā formula ir masīva formula, lai to aktivizētu, nospiediet Ctrl+Shift+Enter.
Atrodiet pirmo teksta rakstzīmi pēc numura
Līdzīgi kā virknes pirmā numura atrašanai, mēs izmantojam funkciju FIND, lai noteiktu, kur teksts sākas no jauna pēc numura, labi izmantojot arī funkcijas start_num argumentu.
| 1 | = MIN (IFERROR (MEKLĒT ({"a", "b", "c", "d", "e", "f", "g", "h", "es", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Šī formula darbojas ļoti līdzīgi kā iepriekšējā, ko izmantoja pirmā numura atrašanai, tikai mēs izmantojam burtus savā masīva konstantē, un tāpēc skaitļi rada #VALUE kļūdas. Lūdzu, ņemiet vērā, ka mēs sākam meklēt tikai pēc pirmā numura noteiktās pozīcijas (tas būs arguments start_num), nevis no virknes sākuma.
Neaizmirstiet nospiest Ctrl+Shift+Enter, lai izmantotu iepriekš minēto formulu.
Neatliek nekas cits kā visu salikt kopā.
Izvilkums no diviem tekstiem
Kad mums ir skaitļa daļas sākuma pozīcija un teksta daļas sākums pēc tam, mēs vienkārši izmantojam funkciju MID, lai iegūtu vajadzīgo skaitļa daļu.
| 1 | = MID (B3, C3, D3-C3) |
Cita metode
Neizskaidrojot to sīkāk, varat izmantot arī zemāk esošo sarežģīto formulu, lai iegūtu numuru no virknes iekšpuses.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))))), 1))*ROW (NETIEŠS ("1:" & LEN (B3)) )), 0), ROW (netiešs ("1:" & LEN (B3))))+1,1)*10^ROW (netiešs ("1:" & LEN (B3)))/10) |
Lūdzu, ņemiet vērā, ka šī augstākminētā formula sniegs jums 0, ja virknē nav atrasts skaitlis un ka sākuma nulles tiks izlaistas.
Atrodiet un izvelciet numuru no virknes uz tekstu Google izklājlapās
Iepriekš redzamie piemēri darbojas Google izklājlapās tāpat kā programmā Excel.
