Az Excelben nincs beépített funkció a számok kinyerésére egy cellában lévő karakterláncból (és fordítva - távolítsa el a numerikus részt, és bontsa ki a szövegrészt egy alfanumerikus karakterláncból).
Ez azonban megtehető egy Excel funkcióval vagy valamilyen egyszerű VBA-kóddal.
Először hadd mondjam el, miről beszélek.
Tegyük fel, hogy van egy adatkészlete, mint az alábbiakban, és számokat szeretne kinyerni a karakterláncokból (például lent):
A választott módszer az Excel használt verziójától is függ:
- Az Excel 2016 előtti verzióinál valamivel hosszabb képletet kell használnia
- Az Excel 2016-hoz használhatja az újonnan bevezetett TEXTJOIN függvényt
- A VBA módszer az Excel összes verziójához elérhető
tartalom
Számok kinyerése karakterláncokból az Excelben (Excel 2016 képletek)
Ez a képlet csak az Excel 2016-ban működik, mert az újonnan bevezetett TEXTJOIN függvényt használja.
Ezenkívül ez a képlet kivonhatja a számokat egy szöveges karakterlánc elején, végén vagy közepén.
Ne feledje, hogy az ebben a részben leírt TEXTJOIN képlet az összes numerikus karaktert összerakja.Például, ha a szöveg „10 jegy 200 dollár”, az eredmény 10200.
Tegyük fel, hogy az alábbihoz hasonló adatkészlettel rendelkezik, és számokat szeretne kinyerni az egyes cellákban található karakterláncokból:
Az alábbi képlet megadja a karakterlánc numerikus részét az Excelben.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Ez egy tömbképlet, ezért a " Control + Shift + Enter " az Enter használata helyett.
Ez a képlet üresen (üres karakterlánc) tér vissza, ha a szövegben nincsenek számok.
Hogyan működik ez a képlet?
Hadd bontom le ezt a képletet, és próbáljam megmagyarázni, hogyan működik:
- ROW(INDIRECT(“1:”&LEN(A2))) – A képlet ezen része egytől kezdődő számsort ad.A képlet LEN függvénye a karakterláncban lévő karakterek teljes számát adja vissza.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - A képlet ezen része egy #ÉRTÉK tömböt ad vissza!Karakterlánc alapú hibák vagy számok.A karakterlánc összes szöveges karaktere #ÉRTÉK lesz!A hibák és minden érték változatlan marad.Ez akkor történik, ha a MID függvényt megszorozzuk 1-gyel.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Az IFERROR függvény használatakor az összes #ÉRTÉK eltávolításra kerül!Hiba, csak számok maradnak.Ennek a résznek a kimenete így néz ki - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – A TEXTJOIN függvény most egyszerűen egyesíti a karakterláncokat. Még mindig (csak számok), és figyelmen kívül hagyja az üres karakterláncokat.
Profi tipp:Ha ellenőrizni szeretné a képlet egy részének kimenetét, jelölje ki a cellát, nyomja meg az F2 billentyűt a szerkesztési módba lépéshez, válassza ki a képletnek azt a részét, amelyet ki szeretne adni, és nyomja meg az F9 billentyűt.Az eredményeket azonnal látni fogja.Ezután ne felejtse el megnyomni a Control + Z vagy az Escape billentyűt.Ne nyomja meg az entert.
Ugyanezt a logikát használhatja szövegrészek alfanumerikus karakterláncokból történő kivonására is.Íme a képlet a szövegrész lekéréséhez 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),""))
A képlet egy kis változása, hogy az IF függvényt arra használjuk, hogy ellenőrizzük a MID függvényből kapott tömbben a hibákat.Ha ez hiba, tartsa meg az értéket, ellenkező esetben cserélje ki üresre.
Ezután használja a TEXTJOIN-t az összes szövegkarakter kombinálásához.
注意: Bár ez a képlet érvényes, illékony függvényt (az INDIRECT függvényt) használ.Ez azt jelenti, hogy ha hatalmas adatkészlettel használja, eltarthat egy ideig, amíg eredményeket kap.A legjobb, ha biztonsági másolatot készít, mielőtt ezt a képletet Excelben használná.
Számok kinyerése karakterláncokból az Excelben (Excel 2013/2010/2007 esetén)
Ha Excel 2013. 2010. vagy 2007. verziója van, akkor nem használhat TEXTJOIN képleteket, ezért ehhez összetett képleteket kell használnia.
Tegyük fel, hogy van egy adatkészlete, mint az alábbi, és ki szeretné bontani az összes számot karakterláncokban minden cellában.
Az alábbi képlet ezt teszi:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(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))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Ez a képlet üresen (üres karakterlánc) tér vissza, ha a szövegben nincsenek számok.
Bár ez egy tömbképlet, teNincs szükségHasználja a "Control-Shift-Enter" parancsot.Ehhez a képlethez egy egyszerű bevitel működik.
A képlet elismerése a csodálatosakat illetiMr. Excel fórum.
Ez a képlet ismét kivonja a karakterlánc összes számát, függetlenül a pozíciótól.Például, ha a szöveg „10 jegy 200 dollár”, az eredmény 10200.
注意: Bár ez a képlet érvényes, illékony függvényt (az INDIRECT függvényt) használ.Ez azt jelenti, hogy ha hatalmas adatkészlettel használja, eltarthat egy ideig, amíg eredményeket kap.A legjobb, ha biztonsági másolatot készít, mielőtt ezt a képletet Excelben használná.
Szöveg és számok elkülönítése Excelben VBA segítségével
Ha gyakran kell elválasztania a szöveget és a számokat (vagy számokat kell kivonnia a szövegből), használhatja a VBA módszert is.
Mindössze annyit kell tennie, hogy egyszerű VBA-kóddal létrehoz egy egyéni felhasználói függvényt (UDF) az Excelben, majd ezt a VBA-képletet használja egy hosszú és összetett képlet helyett.
Hadd mutassam meg, hogyan hozhat létre két képletet a VBA-ban – egyet a számok kinyerésére, egyet pedig a szöveg kivonására a karakterláncokból.
Számok kinyerése sztringekből az Excelben (VBA használatával)
Ebben a részben megmutatom, hogyan hozhat létre egyéni függvényt, hogy egy karakterláncból csak a numerikus részt kapja meg.
Íme a VBA-kód, amelyet az egyéni funkció létrehozásához használunk:
Függvény GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 - StringLength If IsNumeric(Mid(CellRef, i, 1)) Akkor Eredmény = Eredmény & Közép(CellRef, 1) Következő i, GetNumeric = Eredményzáró függvény
Íme a lépések a függvény létrehozásához, majd a munkalapon való használatához:
Most már használhatja a GetText függvényt a munkalapon.Mivel magában a kódban mindent megtettünk, csak a =GetNumeric(A2) képletet kell használnia.
Ez azonnal megkapja a karakterlánc numerikus részét.
Vegye figyelembe, hogy mivel a munkafüzet most VBA-kódot tartalmaz, .xls vagy .xlsm kiterjesztéssel kell mentenie.
Ha gyakran kell használnia ezt a képletet, elmentheti a személyes makró-munkafüzetébe is.Ez lehetővé teszi, hogy ezt az egyéni képletet bármely Excel-munkafüzetben használja.
Szöveg kibontása a karakterláncból az Excelben (VBA használatával)
Ebben a részben megmutatom, hogyan hozhat létre egyéni függvényt, hogy egy karakterláncból csak a szövegrész kerüljön ki.
Íme a VBA-kód, amelyet az egyéni funkció létrehozásához használunk:
Függvény GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 - StringLength Ha nem (IsNumeric(Mid(CellRef, i, 1))) then Eredmény = Eredmény & Mid(CellRef, i, ) Következő i GetText = Eredményzáró függvény
Íme a lépések a függvény létrehozásához, majd a munkalapon való használatához:
Most már használhatja a GetNumeric függvényt a munkalapon.Mivel magában a kódban mindent megtettünk, csak a =GetText(A2) képletet kell használnia.
Ez azonnal megkapja a karakterlánc numerikus részét.
Vegye figyelembe, hogy mivel a munkafüzet most VBA-kódot tartalmaz, .xls vagy .xlsm kiterjesztéssel kell mentenie.
Ha gyakran kell használnia ezt a képletet, elmentheti a személyes makró-munkafüzetébe is.Ez lehetővé teszi, hogy ezt az egyéni képletet bármely Excel-munkafüzetben használja.
