Daar is geen ingeboude funksie in Excel om getalle uit 'n string in 'n sel te onttrek nie (en omgekeerd - verwyder die numeriese deel en onttrek die teksgedeelte uit 'n alfanumeriese string).
Dit kan egter gedoen word deur 'n Excel-funksie of 'n eenvoudige VBA-kode te gebruik.
Laat ek jou eers vertel waarvan ek praat.
Gestel jy het 'n datastel soos hieronder en jy wil getalle uit stringe onttrek (soos hieronder):
Die metode wat jy kies hang ook af van die weergawe van Excel wat jy gebruik:
- Vir weergawes van Excel voor 2016, moet jy 'n effens langer formule gebruik
- Vir Excel 2016 kan jy die nuut ingevoerde TEXTJOIN-funksie gebruik
- VBA-metode beskikbaar vir alle weergawes van Excel
Contents [show]
Onttrek getalle uit stringe in Excel (Excel 2016-formules)
Hierdie formule werk net in Excel 2016 omdat dit die nuut ingevoerde TEXTJOIN-funksie gebruik.
Daarbenewens kan hierdie formule getalle aan die begin, einde of middel van 'n teksstring onttrek.
Let daarop dat die TEXTJOIN-formule wat in hierdie afdeling beskryf word, alle numeriese karakters saamvoeg.Byvoorbeeld, as die teks "10 kaartjies is $200" is, is die resultaat 10200.
Gestel jy het 'n datastel soos die volgende, en jy wil getalle uit stringe in elke sel onttrek:
Die formule hieronder sal vir jou die numeriese deel van 'n string in Excel gee.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Dit is 'n skikking formule, so jy moet gebruik " Beheer + Shift + Enter " in plaas daarvan om Enter te gebruik.
Hierdie formule sal leeg (leë string) terugstuur as daar geen nommers in die teksstring is nie.
Hoe werk hierdie formule?
Kom ek breek hierdie formule af en probeer verduidelik hoe dit werk:
- ROW(INDIRECT(“1:”&LEN(A2))) – Hierdie deel van die formule sal 'n reeks getalle gee wat van een begin.Die LEN-funksie in die formule gee die totale aantal karakters in die string terug.在“成本為 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,RY(INDIREK("1:"&LEN(A2))),1)*1) - Hierdie deel van die formule sal 'n skikking van #WAARDES terugstuur!Stringgebaseerde foute of getalle.Alle tekskarakters in die string word #WAARDE!Foute en alle waardes bly soos dit is.Dit gebeur wanneer ons die MID-funksie met 1 vermenigvuldig.
- IFERROR((MID(A2,RY(INDIREK(“1:”&LEN(A2))),1)*1),””) – Wanneer die IFERROR-funksie gebruik word, word alle #VALUEs verwyder!Fout, slegs nommers sal oorbly.Die uitset van hierdie deel lyk so - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,RY(INDIRECT("1:"&LEN(A2))),1)*1),"")) – TEXTJOIN-funksie kombineer nou eenvoudig stringkarakters Nog steeds daar (slegs getalle) en ignoreer leë stringe.
Pro wenk:As jy die uitvoer van 'n gedeeltelike formule wil nagaan, kies die sel, druk F2 om die wysigingsmodus te betree, kies die deel van die formule wat jy wil uitvoer, en druk F9.Jy sal dadelik die resultate sien.Onthou dan om Control + Z te druk of Escape te druk.Moenie enter druk nie.
Jy kan ook dieselfde logika gebruik om dele van teks uit alfanumeriese stringe te onttrek.Hier is die formule om die teksgedeelte van die string af te kry:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
'n Klein verandering aan hierdie formule is dat die IF-funksie gebruik word om die skikking wat ons van die MID-funksie kry, na te gaan vir foute.As dit 'n fout is, hou die waarde, anders vervang dit met leeg.
Gebruik dan TEXTJOIN om alle tekskarakters te kombineer.
注意: Alhoewel hierdie formule geldig is, gebruik dit 'n vlugtige funksie (die INDIREKTE funksie).Dit beteken dat as jy dit met 'n groot datastel gebruik, dit 'n rukkie kan neem om vir jou resultate te gee.Dit is die beste om 'n rugsteun te skep voordat u hierdie formule in Excel gebruik.
Onttrek getalle uit stringe in Excel (vir Excel 2013/2010/2007)
As jy Excel 2013. 2010. of 2007 het, kan jy nie TEXTJOIN-formules gebruik nie, so jy moet komplekse formules gebruik om dit te doen.
Gestel jy het 'n datastel soos hieronder en jy wil alle getalle in stringe in elke sel onttrek.
Die formule hieronder sal dit doen:
=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),"")
Hierdie formule sal leeg (leë string) terugstuur as daar geen nommers in die teksstring is nie.
Alhoewel dit 'n skikkingsformule is, kan jyNie nodig nieGebruik "Control-Shift-Enter" om dit te gebruik.'n Eenvoudige invoer werk vir hierdie formule.
Krediet vir hierdie formule gaan aan die wonderlikeMnr Excel Forum.
Weereens, hierdie formule sal alle getalle in die string onttrek, ongeag die posisie.Byvoorbeeld, as die teks "10 kaartjies is $200" is, is die resultaat 10200.
注意: Alhoewel hierdie formule geldig is, gebruik dit 'n vlugtige funksie (die INDIREKTE funksie).Dit beteken dat as jy dit met 'n groot datastel gebruik, dit 'n rukkie kan neem om vir jou resultate te gee.Dit is die beste om 'n rugsteun te skep voordat u hierdie formule in Excel gebruik.
Skei teks en getalle in Excel met VBA
As jy gereeld teks en nommers moet skei (of nommers uit teks moet onttrek), kan jy ook die VBA-metode gebruik.
Al wat jy hoef te doen is om 'n pasgemaakte gebruikergedefinieerde funksie (UDF) in Excel te skep deur eenvoudige VBA-kode te gebruik, en gebruik dan daardie VBA-formule in plaas van 'n lang en komplekse formule.
Kom ek wys jou hoe om twee formules in VBA te skep - een om getalle te onttrek en een om teks uit stringe te onttrek.
Onttrek getalle uit stringe in Excel (met VBA)
In hierdie deel sal ek jou wys hoe om 'n pasgemaakte funksie te skep om slegs die numeriese deel van 'n string te kry.
Hier is die VBA-kode wat ons sal gebruik om hierdie persoonlike funksie te skep:
Funksie GetNumeric(SellRef As String) Dim StringLength As Heelgetal StringLength = Len(SellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Resultaat Eindfunksie
Hier is die stappe om hierdie funksie te skep en dit dan in 'n werkblad te gebruik:
Jy sal nou die GetText-funksie op die werkblad kan gebruik.Aangesien ons reeds al die swaar take in die kode self gedoen het, hoef u net die formule =GetNumeric(A2) te gebruik.
Dit sal jou dadelik die numeriese deel van die string gee.
Let daarop dat aangesien die werkboek nou VBA-kode bevat, moet jy dit stoor met 'n .xls- of .xlsm-uitbreiding.
As jy hierdie formule gereeld moet gebruik, kan jy dit ook in jou persoonlike makro-werkboek stoor.Dit sal jou toelaat om hierdie pasgemaakte formule te gebruik in enige Excel-werkboek wat jy gebruik.
Onttrek teks uit string in Excel (met VBA)
In hierdie deel sal ek jou wys hoe om 'n pasgemaakte funksie te skep om slegs die teksgedeelte van 'n string te kry.
Hier is die VBA-kode wat ons sal gebruik om hierdie persoonlike funksie te skep:
Funksie GetText(CellRef As String) Dim StringLength As Heelgetal StringLength = Len(CellRef) Vir i = 1 tot StringLength Indien nie (IsNumeric(Mid(CellRef, i, 1))) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) ) Volgende i GetText = Resultaat Eindfunksie
Hier is die stappe om hierdie funksie te skep en dit dan in 'n werkblad te gebruik:
Jy sal nou die GetNumeric-funksie in die werkblad kan gebruik.Aangesien ons al die swaar take in die kode self gedoen het, is al wat jy hoef te doen die formule =GetText(A2) te gebruik.
Dit sal jou dadelik die numeriese deel van die string gee.
Let daarop dat aangesien die werkboek nou VBA-kode bevat, moet jy dit stoor met 'n .xls- of .xlsm-uitbreiding.
As jy hierdie formule gereeld moet gebruik, kan jy dit ook in jou persoonlike makro-werkboek stoor.Dit sal jou toelaat om hierdie pasgemaakte formule te gebruik in enige Excel-werkboek wat jy gebruik.
