Videó megtekintése - Hogyan lehet kinyerni számokat a szöveg karakterláncából Excelben (a képlet és a VBA használatával)
Az Excelben nincs beépített funkció a számok kibontásához egy cellában lévő karakterláncból (vagy fordítva - távolítsa el a numerikus részt, és bontsa ki a szöveges részt egy alfanumerikus karakterláncból).
Ez azonban Excel -funkciók koktélja vagy néhány egyszerű VBA -kód segítségével történhet.
Először hadd mutassam meg, miről beszélek.
Tegyük fel, hogy rendelkezik adathalmazzal az alábbiak szerint, és ki akarja vonni a számokat a karakterláncból (az alábbiak szerint):
A választott módszer az Excel Excel verziójától is függ:
- Az Excel 2016 előtti verzióknál valamivel hosszabb képleteket kell használnia
- Az Excel 2016 esetében használhatja az újonnan bevezetett TEXTJOIN függvényt
- A VBA módszer az Excel összes verziójában használható
Kattintson ide a mintafájl letöltéséhez
Számok kivonása a karakterláncból Excelben (Formula for Excel 2016)
Ez a képlet csak az Excel 2016 programban fog működni, mivel az újonnan bevezetett TEXTJOIN függvényt használja.
Ezenkívül ez a képlet kivonhatja a szöveges karakterlánc elején, végén vagy közepén található számokat.
Ne feledje, hogy az ebben a részben ismertetett TEXTJOIN képlet az összes numerikus karaktert tartalmazza. Például, ha a szöveg „10 jegy ára 200 USD”, akkor 10200 lesz az eredmény.Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és ki szeretné vonni a számokat az egyes cellák karakterláncaiból:
Az alábbi képlet megadja az Excel karakterláncának numerikus részét.
= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, SOR (KÖZVETLEN ("1:" & LEN (A2))), 1)*1), ""))
Ez egy tömbképlet, ezért a „Control + Shift + Enter’Az Enter használata helyett.
Abban az esetben, ha nincsenek számok a szöveges karakterláncban, ez a képlet üres (üres karakterláncot) ad vissza.
Hogyan működik ez a képlet?
Hadd törjem meg ezt a képletet, és próbáljam megmagyarázni, hogyan működik:
- SOR (KÖZVETLEN („1:” & LEN (A2))) - a képlet ezen része az egyből kiinduló számsort adna. A képlet LEN függvénye visszaadja a karakterlánc összes karakterét. „A költség 100 USD” esetén 19 -et ad vissza. A képletek így sorossá válnak (KÖZVETLEN („1:19”). A SOR függvény ezután egy számsorozatot ad vissza - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, SOR (KÖZVETLEN („1:” & LEN (A2)))), 1)*1) - A képlet ezen része #ÉRTÉK tömböt adna vissza! hibák vagy számok a karakterlánc alapján. A karakterlánc összes szöveges karaktere #ÉRTÉK lesz! hibák és minden számérték a jelenlegi állapotában marad. Ez akkor történik, amikor a MID függvényt megszoroztuk 1 -gyel.
- IFERROR ((MID (A2, SOR (KÖZVETLEN („1:” & LEN (A2))))), 1)*1), ””) - IFERROR függvény használata esetén az összes #ÉRTÉK! hibák, és csak a számok maradnak. Ennek a résznek a kimenete így nézne ki: {“”; ””; ””; ””; ””; ””; ”; ""; ""; ""; 1; 0; 0}
- = TEXTJOIN („”, TRUE, IFERROR ((MID (A2, SOR (KÖZVETLEN („1:” & LEN (A2))), 1)*1), ””)) - A TEXTJOIN függvény most egyszerűen kombinálja a karakterláncokat marad (amelyek csak a számok), és figyelmen kívül hagyja az üres karakterláncot.
Töltse le a példa fájlt
Ugyanezt a logikát használhatja a szövegrész kibontására is egy alfanumerikus karakterláncból. Az alábbi képlet megadja a szöveges részt a karakterláncból:
= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN) (A2))), 1), ""))
Egy kisebb változtatás ebben a képletben az, hogy az IF függvény ellenőrzi, hogy a MID függvényből kapott tömb hibás -e vagy sem. Ha hiba, akkor megtartja az értéket, máskülönben üresre cseréli.
Ezután a TEXTJOIN az összes szöveges karakter kombinálására szolgál.
Vigyázat: Bár ez a képlet remekül működik, illékony függvényt használ (az INDIRECT függvény). Ez azt jelenti, hogy ha ezt egy hatalmas adatkészlettel használja, akkor eltarthat egy ideig, amíg megadja az eredményeket. A legjobb, ha biztonsági másolatot készít, mielőtt ezt a képletet használja az Excelben.Számok kivonása a karakterláncból az Excelben (Excel 2013/2010/2007)
Ha Excel 2013. 2010. vagy 2007 -es verziója van, akkor nem használhatja a TEXTJOIN képletet, ezért bonyolult képletet kell használnia ennek elvégzéséhez.
Tegyük fel, hogy rendelkezik egy adathalmazzal, amint az alább látható, és ki szeretné vonni az egyes cellákban lévő karakterlánc összes számát.
Ezt az alábbi képlet teszi lehetővé:
= HA (SUM (LEN (A2) -LEN (Helyettesítő (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))>> SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2)))))), 1)) * SOR (KÖZVETLEN ("$ 1: $" & LEN (A2))), 0), SOR (KÖZVETLEN ("$ 1: $" & LEN (A2)))))+1,1) * 10^SOR (KÖZVETLEN ("$ 1: $" & LEN (A2)))/10), "")
Abban az esetben, ha nincs szám a szöveges karakterláncban, ez a képlet üresen tér vissza (üres karakterlánc).
Bár ez tömbképlet, te nem kell ehhez használja a „Control-Shift-Enter” billentyűt. Egy egyszerű enter működik ehhez a képlethez.
Ennek a képletnek köszönhetjük a csodálatos Excel Excel fórumot.
Ez a képlet ismét kinyomtatja a karakterlánc összes számát, függetlenül a pozíciótól. Például, ha a szöveg „10 jegy ára 200 USD”, akkor 10200 lesz az eredmény.
Vigyázat: Bár ez a képlet remekül működik, illékony függvényt használ (az INDIRECT függvény). Ez azt jelenti, hogy ha ezt egy hatalmas adatkészlettel használja, akkor eltarthat egy ideig, amíg megadja az eredményeket. A legjobb, ha biztonsági másolatot készít, mielőtt ezt a képletet használja az Excelben.Szöveg és számok különválasztása Excelben a VBA használatával
Ha a szöveg és a számok elválasztása (vagy számok kivonása a szövegből) gyakran szükséges, akkor a VBA módszert is használhatja.
Mindössze annyit kell tennie, hogy egy egyszerű VBA -kódot használ az egyéni felhasználói definiált függvény (UDF) létrehozásához az Excelben, majd a hosszú és bonyolult képletek helyett használja ezt a VBA -képletet.
Hadd mutassam meg, hogyan hozhat létre két képletet a VBA -ban - az egyik számok kinyerésére, a másik pedig a szöveg kivonására egy karakterláncból.
Számok kivonása a karakterláncból Excelben (VBA használatával)
Ebben a részben megmutatom, hogyan lehet létrehozni az egyéni függvényt, hogy csak a numerikus részt kapja meg egy karakterláncból.
Az alábbiakban bemutatjuk a VBA kódot, amelyet az egyéni funkció létrehozásához használunk:
Funkció GetNumeric (CellRef mint karakterlánc) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Eredményvégfüggvény
Az alábbi lépésekkel hozhatja létre ezt a funkciót, majd használhatja azt a munkalapon:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic elemre (Használhatja az ALT + F11 billentyűparancsot is)
- A megnyíló VB Editor háttérben kattintson a jobb gombbal a munkafüzet bármelyik objektumára.
- Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ez beszúrja a munkafüzet modulobjektumát.
- A Modul kód ablakban másolja ki és illessze be a fent említett VBA kódot.
- Zárja be a VB szerkesztőt.
Most már használhatja a GetText funkciót a munkalapon. Mivel magunk a kódot elvégeztük, csak a = GetNumeric (A2) képletet kell használnunk.
Ez azonnal csak a karakterlánc numerikus részét adja meg.
Vegye figyelembe, hogy mivel a munkafüzetben most VBA -kód található, el kell mentenie .xls vagy .xlsm kiterjesztéssel.
Töltse le a példa fájlt
Ha ezt a képletet gyakran kell használnia, akkor azt elmentheti a személyes makró munkafüzetébe. Ez lehetővé teszi, hogy ezt az egyéni képletet bármelyik Excel munkafüzetben használhassa.
Szöveg kivonása egy karakterláncból Excelben (VBA használatával)
Ebben a részben megmutatom, hogyan lehet létrehozni az egyéni függvényt, hogy csak a szöveges részt kapja meg egy karakterláncból.
Az alábbiakban bemutatjuk a VBA kódot, amelyet az egyéni funkció létrehozásához használunk:
Funkció GetText (CellRef mint karakterlánc) Dim StringLength mint Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Aztán Result = Result & Mid (CellRef, i, 1 ) Következő i GetText = Eredmény vége funkció
Az alábbi lépésekkel hozhatja létre ezt a funkciót, majd használhatja azt a munkalapon:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic elemre (Használhatja az ALT + F11 billentyűparancsot is)
- A megnyíló VB Editor háttérben kattintson a jobb gombbal a munkafüzet bármelyik objektumára.
- Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ez beszúrja a munkafüzet modulobjektumát.
- Ha már rendelkezik modullal, kattintson rá duplán (nem kell újat beilleszteni, ha már megvan).
- Ha már rendelkezik modullal, kattintson rá duplán (nem kell újat beilleszteni, ha már megvan).
- A Modul kód ablakban másolja ki és illessze be a fent említett VBA kódot.
- Zárja be a VB szerkesztőt.
Most már használhatja a GetNumeric funkciót a munkalapon. Mivel mi magunk végeztük el a nehéz emelést a kódban, mindössze annyit kell tennie, hogy a = GetText (A2) képletet használja.
Ez azonnal csak a karakterlánc numerikus részét adja meg.
Vegye figyelembe, hogy mivel a munkafüzetben most VBA -kód található, el kell mentenie .xls vagy .xlsm kiterjesztéssel.
Ha ezt a képletet gyakran kell használnia, akkor azt elmentheti a személyes makró munkafüzetébe is. Ez lehetővé teszi, hogy ezt az egyéni képletet bármelyik Excel munkafüzetben használhassa.
Ha Excel 2013 -at vagy korábbi verziókat használ, és nincs
