✅ Ištraukite skaičius iš eilutės „Excel“ (naudodami formules arba VBA)

Turinys

Skaičių ištraukimas iš eilutės „Excel“ („Excel 2016“ formulė)
Skaičių ištraukimas iš eilutės „Excel“ („Excel 2013/2010/2007“)
Atskirkite tekstą ir skaičius „Excel“ naudodami VBA

Žiūrėti vaizdo įrašą - Kaip ištraukti skaičius iš teksto eilutės „Excel“ (naudojant formulę ir VBA)

„Excel“ nėra integruotos funkcijos, leidžiančios išgauti skaičius iš eilutės ląstelėje (arba atvirkščiai - pašalinkite skaitinę dalį ir ištraukite tekstinę dalį iš raidinės ir skaitmeninės eilutės).

Tačiau tai galima padaryti naudojant „Excel“ funkcijų kokteilį arba paprastą VBA kodą.

Pirmiausia leiskite man parodyti, apie ką aš kalbu.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite išgauti skaičius iš eilutės (kaip parodyta žemiau):

Pasirinktas metodas taip pat priklausys nuo naudojamos „Excel“ versijos:

Versijoms iki „Excel 2016“ turite naudoti šiek tiek ilgesnes formules
„Excel 2016“ galite naudoti naujai pristatytą funkciją TEXTJOIN
VBA metodas gali būti naudojamas visose „Excel“ versijose

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Skaičių ištraukimas iš eilutės „Excel“ („Excel 2016“ formulė)

Ši formulė veiks tik „Excel 2016“, nes ji naudoja naujai pristatytą funkciją TEXTJOIN.

Be to, ši formulė gali išgauti skaičius, esančius teksto eilutės pradžioje, pabaigoje arba viduryje.

Atminkite, kad šiame skyriuje aprašyta TEXTJOIN formulė suteiks visus skaitinius simbolius kartu. Pavyzdžiui, jei tekstas yra „10 bilietų kaina yra 200 USD“, rezultatas bus 10 200.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite išgauti skaičius iš kiekvienos ląstelės eilučių:

Žemiau yra formulė, kuri suteiks jums skaitinę dalį iš eilutės „Excel“.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), ""))

Tai masyvo formulė, todėl turite naudoti „„Control“ + „Shift“ + „Enter““, O ne naudoti„ Enter “.

Jei teksto eilutėje nėra skaičių, ši formulė grąžins tuščią (tuščią eilutę).

Kaip veikia ši formulė?

Leiskite man sulaužyti šią formulę ir pabandyti paaiškinti, kaip ji veikia:

ROW (NETIESIOGINĖ („1:“ & LEN (A2))) - ši formulės dalis suteiktų skaičių seriją, prasidedančią nuo vieno. Funkcija LEN formulėje grąžina bendrą simbolių skaičių eilutėje. „Kaina yra 100 USD“ atveju grąžinama 19. Taigi formulės taps ROW (NETIESIOGINĖ („1:19“). Tada funkcija ROW grąžins skaičių seriją - {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, ROW (NETIESIOGINIS („1:“ & LEN (A2))))), 1)*1) - Ši formulės dalis grąžins #VALUE masyvą! klaidų ar skaičių, pagrįstų eilute. Visi eilutės teksto simboliai tampa #VALUE! klaidos ir visos skaitinės vertės lieka tokios, kokios yra. Tai atsitinka, kai MID funkciją padauginome iš 1.
IFERROR ((MID (A2, ROW (INDIRECT („1:“ & LEN (A2)))))), 1)*1), ““) - Kai naudojama IFERROR funkcija, ji pašalins visas #VALUE! klaidų ir liktų tik skaičiai. Šios dalies išvestis atrodytų taip - {“”; ””; ””; ””; ””; ""; ""; ""; 1; 0; 0}
= TEXTJOIN („“, TRUE, IFERROR ((MID (A2, ROW (NETIESIOGINĖ („1:“ & LEN (A2))), 1)*1), ““)) - TEXTJOIN funkcija dabar tiesiog sujungia eilutės simbolius kuris lieka (tai tik skaičiai) ir ignoruoja tuščią eilutę.

Pro patarimas: Jei norite patikrinti formulės dalies išvestį, pasirinkite langelį, paspauskite F2, kad įeitumėte į redagavimo režimą, pasirinkite formulės dalį, kuriai norite išvesties, ir paspauskite F9. Jūs iškart pamatysite rezultatą. Tada nepamirškite paspausti „Control“ + Z arba paspausti klavišą „Escape“. NESPAUSKITE įvesties klavišo.

Atsisiųskite pavyzdinį failą

Taip pat galite naudoti tą pačią logiką, norėdami išgauti teksto dalį iš raidinės ir skaitinės eilutės. Žemiau yra formulė, kuri gautų tekstinę dalį iš eilutės:

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2)))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN) (A2))), 1), ""))

Nedidelis šios formulės pakeitimas yra tas, kad IF funkcija naudojama patikrinti, ar masyvas, kurį gauname iš MID funkcijos, yra klaidos. Jei tai klaida, ji išlaiko vertę, kitaip ją pakeičia tuščia.

Tada TEXTJOIN naudojamas sujungti visus teksto simbolius.

Atsargiai: Nors ši formulė puikiai veikia, ji naudoja nepastovią funkciją (INDIRECT funkcija). Tai reiškia, kad jei naudosite tai su didžiuliu duomenų rinkiniu, rezultatų pateikimas gali užtrukti. Prieš naudojant šią formulę „Excel“, geriausia sukurti atsarginę kopiją.

Skaičių ištraukimas iš eilutės „Excel“ („Excel 2013/2010/2007“)

Jei turite „Excel 2013. 2010.“ arba „2007“, negalite naudoti „TEXTJOIN“ formulės, todėl turėsite naudoti sudėtingą formulę.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite išgauti visus kiekvienos ląstelės eilutės skaičius.

Žemiau pateikta formulė tai padarys:

= IF (SUM (LEN (A2) -LEN (PAKAITA (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2)))))))))))), 1)) * EILUTĖ (NETIESIOGINĖ („$ 1: $“ & LEN (A2))), 0), EILUTĖ (NETIESIOGINĖ („$ 1: $“ ir LEN (A2)))))+1,1) * 10^EILUTĖ (NETIESIOGINĖ ("$ 1: $" ir LEN (A2)))/10), "")

Jei teksto eilutėje nėra skaičiaus, ši formulė grąžins tuščią (tuščią eilutę).

Nors tai yra masyvo formulė, jūs nereikia Norėdami tai padaryti, naudokite „Control-Shift-Enter“. Šiai formulei tinka paprastas įvedimas.

Šios formulės nuopelnas atitenka nuostabiam pono „Excel“ forumui.

Vėlgi, ši formulė išskleis visus eilutės skaičius, nepriklausomai nuo padėties. Pavyzdžiui, jei tekstas yra „10 bilietų kaina yra 200 USD“, rezultatas bus 10 200.

Atskirkite tekstą ir skaičius „Excel“ naudodami VBA

Jei dažnai reikia atskirti tekstą ir skaičius (arba ištraukti skaičius iš teksto), taip pat galite naudoti VBA metodą.

Viskas, ką jums reikia padaryti, tai naudoti paprastą VBA kodą, kad sukurtumėte pasirinktinę vartotojo apibrėžtą funkciją (UDF) programoje „Excel“, tada vietoj ilgų ir sudėtingų formulių naudokite tą VBA formulę.

Leiskite man parodyti, kaip sukurti dvi formules VBA - vieną skaičių išgauti, kitą - ištraukti tekstą iš eilutės.

Ištraukite skaičius iš eilutės „Excel“ (naudodami VBA)

Šioje dalyje parodysiu, kaip sukurti pasirinktinę funkciją, kad iš eilutės būtų gauta tik skaitinė dalis.

Žemiau yra VBA kodas, kurį naudosime kurdami šią pasirinktinę funkciją:

Funkcija „GetNumeric“ („CellRef“ kaip eilutė) „Dim String“ GetNumeric = Rezultato pabaigos funkcija

Štai kaip sukurti šią funkciją ir tada ją naudoti darbalapyje:

Eikite į skirtuką Kūrėjas.
Spustelėkite „Visual Basic“ (taip pat galite naudoti sparčiuosius klavišus ALT + F11)
Atsidariusioje VB redaktoriaus vidinėje sistemoje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą.
Eikite į „Įterpti“ ir spustelėkite „Modulis“. Taip bus įterptas darbo knygos modulio objektas.
Lange „Modulio kodas“ nukopijuokite ir įklijuokite aukščiau paminėtą VBA kodą.
Uždarykite VB redaktorių.

Dabar darbalapyje galėsite naudoti funkciją „GetText“. Kadangi visą sunkų kėlimą atlikome pačiame kode, jums tereikia naudoti formulę = GetNumeric (A2).

Taip akimirksniu gausite tik skaitinę eilutės dalį.

Atminkite, kad dabar darbaknygėje yra VBA kodas, todėl turite ją išsaugoti naudodami .xls arba .xlsm plėtinį.

Atsisiųskite pavyzdinį failą

Jei šią formulę turite naudoti dažnai, taip pat galite ją išsaugoti savo asmeninėje makrokomandos darbaknygėje. Tai leis jums naudoti šią pasirinktinę formulę bet kurioje „Excel“ darbaknygėje, su kuria dirbate.

Ištraukite tekstą iš eilutės „Excel“ (naudodami VBA)

Šioje dalyje parodysiu, kaip sukurti pasirinktinę funkciją, kad iš eilutės gautumėte tik tekstinę dalį.

Žemiau yra VBA kodas, kurį naudosime kurdami šią pasirinktinę funkciją:

Funkcija „GetText“ („CellRef“ kaip eilutė) „Dim String“ Ilgis kaip sveikasis skaičius StringLength = Len („CellRef“) „i = 1“ iki „StringLength“ (jei ne ) Kitas i GetText = Rezultato pabaigos funkcija

Štai kaip sukurti šią funkciją ir tada ją naudoti darbalapyje:

Eikite į skirtuką Kūrėjas.
Spustelėkite „Visual Basic“ (taip pat galite naudoti sparčiuosius klavišus ALT + F11)
Atsidariusioje VB redaktoriaus vidinėje sistemoje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą.
Eikite į „Įterpti“ ir spustelėkite „Modulis“. Taip bus įterptas darbo knygos modulio objektas.
- Jei jau turite modulį, dukart spustelėkite jį (nereikia įdėti naujo, jei jį jau turite).
Lange „Modulio kodas“ nukopijuokite ir įklijuokite aukščiau paminėtą VBA kodą.
Uždarykite VB redaktorių.

Dabar darbalapyje galėsite naudoti funkciją „GetNumeric“. Kadangi visą sunkų kėlimą atlikome pačiame kode, jums tereikia naudoti formulę = GetText (A2).

Taip akimirksniu gausite tik skaitinę eilutės dalį.