✅ Numbrite ekstraktimine Exceli stringist (valemite või VBA abil)

Lang L: none (table-of-contents)

Numbrite ekstraktimine Exceli stringist (Exceli valem 2016)
Numbrite ekstraktimine Exceli stringist (Exceli jaoks 2013/2010/2007)
Eraldage tekst ja numbrid Excelis VBA abil

Vaadake videot - Kuidas Exceli tekstistringist numbreid ekstraheerida (valemi ja VBA abil)

Excelis ei ole sisseehitatud funktsiooni numbrite eraldamiseks lahtri stringist (või vastupidi - eemaldage numbriline osa ja eraldage tekstiosa tähtnumbrilisest stringist).

Seda saab aga teha Exceli funktsioonide kokteili või mõne lihtsa VBA -koodi abil.

Lubage mul kõigepealt teile näidata, millest ma räägin.

Oletame, et teil on allpool näidatud andmekogum ja soovite numbrid stringist välja võtta (nagu allpool näidatud):

Teie valitud meetod sõltub ka teie kasutatava Exceli versioonist.

Versioonidele, mis on varasemad kui Excel 2016, peate kasutama veidi pikemaid valemeid
Excel 2016 jaoks saate kasutada äsja tutvustatud funktsiooni TEXTJOIN
VBA meetodit saab kasutada kõigis Exceli versioonides

Näidisfaili allalaadimiseks klõpsake siin

Numbrite ekstraktimine Exceli stringist (Exceli valem 2016)

See valem töötab ainult rakenduses Excel 2016, kuna see kasutab äsja kasutusele võetud funktsiooni TEXTJOIN.

Samuti saab selle valemiga välja võtta numbrid, mis asuvad tekstistringi alguses, lõpus või keskel.

Pange tähele, et selles jaotises käsitletud valem TEXTJOIN annab teile kõik numbrimärgid kokku. Näiteks kui tekst on „10 pileti hind on 200 USD“, annab see tulemuseks 10200.

Oletame, et teil on allpool näidatud andmekogum ja soovite numbrid iga lahtri stringidest välja võtta:

Allpool on valem, mis annab teile Exceli stringi numbrilise osa.

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

See on massiivivalem, seega peate kasutamaCtrl + Tõstuklahv + Enter"Selle asemel, et kasutada sisestusklahvi.

Kui tekstistringis pole numbreid, tagastab see valem tühja (tühi string).

Kuidas see valem töötab?

Lubage mul murda see valem ja proovida selgitada, kuidas see toimib:

RIDA (KAUDNE (“1:” & LEN (A2))) - see valemiosa annaks numbriseeria alates ühest. Funktsioon LEN valemis tagastab stringi tähemärkide koguarvu. „Maksumus on 100 USD” korral tagastab see 19. Valemid muutuksid seega RIDA (KAUDNE („1:19”). Funktsioon ROW tagastab seejärel numbriseeria - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, ROW (KAUDNE (“1:” & LEN (A2)))), 1)*1) - see valemiosa tagastaks massiivi #VALUE! stringil põhinevad vead või numbrid. Kõik stringi tekstimärgid muutuvad #VALUE! vead ja kõik numbrilised väärtused jäävad samaks. See juhtub siis, kui oleme korrutanud funktsiooni MID 1 -ga.
IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))))), 1)*1), ””) - Funktsiooni IFERROR kasutamisel eemaldab see kõik #VALUE! vigu ja alles jäävad ainult numbrid. Selle osa väljund näeks välja selline - {“”; ””; ””; ””; ””; ””; ””; ""; ""; ""; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””)) - Funktsioon TEXTJOIN ühendab nüüd lihtsalt stringimärgid mis jääb alles (mis on ainult numbrid) ja ignoreerib tühja stringi.

Pro näpunäide: Kui soovite kontrollida valemi osa väljundit, valige lahter, muutmisrežiimi sisenemiseks vajutage klahvi F2, valige see valemiosa, mille väljundit soovite, ja vajutage klahvi F9. Näete tulemust koheselt. Ja siis pidage meeles, kas vajutate klahvikombinatsiooni Control + Z või vajutate klahvi Escape. ÄRGE vajutage sisestusklahvi.

Laadige alla näidisfail

Sama loogika abil saate ka tekstiosa tähtnumbrilisest stringist välja võtta. Allpool on valem, mis saaks stringi tekstiosa:

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

Väike muudatus selles valemis on see, et IF -funktsiooni abil kontrollitakse, kas MID -funktsioonist saadud massiiv on vead või mitte. Kui see on viga, säilitab see väärtuse, muidu asendab selle tühjaga.

Seejärel kasutatakse TEXTJOIN kõigi tekstimärkide ühendamiseks.

Ettevaatust: Kuigi see valem töötab suurepäraselt, kasutab see lenduvat funktsiooni (funktsiooni INDIRECT). See tähendab, et kui kasutate seda tohutu andmekogumiga, võib tulemuste esitamine võtta aega. Enne selle valemi kasutamist Excelis on parem luua varukoopia.

Numbrite ekstraktimine Exceli stringist (Exceli jaoks 2013/2010/2007)

Kui teil on Excel 2013. 2010. või 2007, ei saa te TEXTJOIN valemit kasutada, seega peate selle tegemiseks kasutama keerulist valemit.

Oletame, et teil on andmestik, nagu allpool näidatud, ja soovite eraldada kõik lahtri numbrid stringist.

Selle saab teha järgmise valemiga:

= IF (SUM (LEN (A2) -LEN (ASENDUS (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)) * RIDA (KAUDNE ("$ 1: $" & LEN (A2))), 0), RIDA (KAUDNE ("$ 1: $" & LEN (A2)))))+1,1) * 10^RIDA (KAUDSE ("$ 1: $" ja LEN (A2)))/10), "")

Kui tekstistringis pole numbrit, tagastab see valem tühja (tühi string).

Kuigi see on massiivivalem, teie pole vaja selle kasutamiseks klahvikombinatsiooni „Control-Shift-Enter“. Selle valemi jaoks töötab lihtne sisestus.

Selle valemi tunnustus läheb hämmastavale hr Exceli foorumile.

Jällegi ekstraheerib see valem kõik stringi numbrid olenemata positsioonist. Näiteks kui tekst on „10 pileti hind on 200 USD“, annab see tulemuseks 10200.

Eraldage tekst ja numbrid Excelis VBA abil

Kui teksti ja numbrite eraldamine (või tekstist numbrite väljavõtmine) on sageli vajalik, võite kasutada ka VBA meetodit.

Kõik, mida peate tegema, on kasutada lihtsat VBA -koodi, et luua Excelis kohandatud kasutaja määratud funktsioon (UDF), ja seejärel kasutada seda VBA valemit pikkade ja keeruliste valemite asemel.

Näitan teile, kuidas luua VBA -s kaks valemit - üks numbrite eraldamiseks ja teine stringist teksti väljavõtmiseks.

Väljavõte Exceli numbritest stringist (kasutades VBA -d)

Selles osas näitan teile, kuidas luua kohandatud funktsioon, et saada stringist ainult numbriline osa.

Allpool on VBA -kood, mida me selle kohandatud funktsiooni loomiseks kasutame:

Funktsioon GetNumeric (CellRef kui string) Dim StringLength As Integer String GetNumeric = Tulemuse lõppfunktsioon

Selle funktsiooni loomiseks ja seejärel töölehe kasutamiseks kasutage järgmisi samme.

Minge vahekaardile Arendaja.
Klõpsake Visual Basic (saate kasutada ka kiirklahvi ALT + F11)
Avanevas VB Editori taustaprogrammis paremklõpsake mõnda töövihiku objekti.
Minge sisesta ja klõpsake moodulit. See lisab töövihiku mooduli objekti.
Kopeerige ja kleepige aknas Mooduli kood ülalmainitud VBA -kood.
Sulgege VB redaktor.

Nüüd saate töölehel kasutada funktsiooni GetText. Kuna oleme kõik raskused tõstnud koodis endas, peate vaid kasutama valemit = GetNumeric (A2).

See annab teile koheselt ainult stringi numbrilise osa.

Pange tähele, et kuna töövihikus on nüüd VBA -kood, peate selle salvestama laiendiga .xls või .xlsm.

Laadige alla näidisfail

Kui peate seda valemit sageli kasutama, saate selle salvestada ka oma isiklikku makro töövihikusse. See võimaldab teil seda kohandatud valemit kasutada mis tahes Exceli töövihikus, millega töötate.

Exceli teksti ekstraktimine stringist (kasutades VBA -d)

Selles osas näitan teile, kuidas luua kohandatud funktsiooni, et saada stringist ainult tekstiosa.

Allpool on VBA -kood, mida me selle kohandatud funktsiooni loomiseks kasutame:

Funktsioon GetText (CellRef kui string) Dim String ) Järgmine i GetText = Tulemuse lõppfunktsioon

Selle funktsiooni loomiseks ja seejärel töölehe kasutamiseks kasutage järgmisi samme.

Minge vahekaardile Arendaja.
Klõpsake Visual Basic (saate kasutada ka kiirklahvi ALT + F11)
Avanevas VB Editori taustaprogrammis paremklõpsake mõnda töövihiku objekti.
Minge sisesta ja klõpsake moodulit. See lisab töövihiku mooduli objekti.
- Kui teil on moodul juba olemas, topeltklõpsake seda (uut pole vaja sisestada, kui see on juba olemas).
Kopeerige ja kleepige aknas Mooduli kood ülalmainitud VBA -kood.
Sulgege VB redaktor.