Skip to main content

Conas uimhir a bhaint as sreangán téacs in Excel amháin?


Modh 1: Sliocht uimhir amháin as teaghráin téacs leis an bhfoirmle

Is féidir leis an bhfoirmle fhada seo a leanas cabhrú leat na huimhreacha a bhaint as na teaghráin téacs, déan mar seo le do thoil:

Roghnaigh cill bhán inar mian leat an uimhir eastósctha a aschur, ansin clóscríobh an fhoirmle seo: = SUMPRODUCT (MID (0 & A5, LARGE (INDEX (ISNUMBER (- MID (A5, ROW (INDIRECT ("1:" & LEN (A5)), 1)) * ROW (INDIRECT ("1:" & LEN (A5) )), 0), ROW (INDIRECT ("1:" & LEN (A5)))) + + 1, 1) * 10 ^ ROW (INDIRECT ("1:" & LEN (A5))) / 10), agus ansin tarraing an láimhseáil líonta chun an raon a theastaíonn uait chun an fhoirmle seo a chur i bhfeidhm a líonadh. Féach an pictiúr:

uimhreacha sliocht doc amháin 2

Nótaí:

  • 1. A5 seasann na chéad sonraí a theastaíonn uait uimhreacha a bhaint den liosta amháin.
  • 2. Taispeánfar an toradh mar 0 nuair nach bhfuil aon uimhreacha sa téad.

Sliocht uimhreacha ó teaghráin téacs amháin:

Le Kutools le haghaidh Excel'S BREISNIÚCHÁIN feidhm, ní féidir leat ach uimhreacha a bhaint as na cealla teaghrán téacs go tapa. Cliceáil chun Kutools a íoslódáil le haghaidh Excel!

uimhreacha sliocht doc amháin 14


Modh 2: Sliocht uimhir ó teaghráin téacs le cód VBA amháin

Seo cód VBA ar féidir leis fabhar a dhéanamh duit freisin, déan mar a leanas:

1. Coinnigh síos an Alt + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Sliocht uimhir amháin as sreangán téacs:

Sub ExtrNumbersFromRange()
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. Agus ansin, brúigh F5 eochair chun an cód seo a rith, agus tá bosca pras popped amach le meabhrú duit an raon téacs is mian leat a úsáid a roghnú, féach an scáileán:

uimhreacha sliocht doc amháin 3

4. Ansin, cliceáil OK, tá bosca pras eile á leanúint, roghnaigh cill le do thoil chun an toradh a aschur, féach an scáileán:

uimhreacha sliocht doc amháin 4

5. Faoi dheireadh, cliceáil OK cnaipe, agus baineadh na huimhreacha go léir sna cealla roghnaithe ag an am céanna.


Modh 3: Sliocht uimhir amháin as sreangán téacs le Kutools le haghaidh Excel

Kutools le haghaidh Excel tá feidhm chumhachtach aige freisin ar a dtugtar BREISNIÚCHÁIN, leis an bhfeidhm seo, ní féidir leat ach na huimhreacha a bhaint as na teaghráin téacs bunaidh.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar a leanas le do thoil:

1. Cliceáil cill seachas do shreang téacs ina gcuirfidh tú an toradh, féach an scáileán:

uimhreacha sliocht doc amháin 5

2. Ansin cliceáil Kutools > Feidhmeanna Kutools > Téacs > BREISNIÚCHÁIN, féach ar an scáileán:

uimhreacha sliocht doc amháin 6

3. Sa an Argóintí Feidhm dialóg, roghnaigh cill ar mhaith leat na huimhreacha a bhaint as an Teacs bosca téacs, agus ansin iontráil fíor or bréagach isteach sa N bosca téacs, féach an pictiúr:

uimhreacha sliocht doc amháin 7

nótaí: an argóint N is mír roghnach é, má iontrálann tú fíor, cuirfidh sé na huimhreacha ar ais mar uimhriúil, má iontrálann tú bréagach, cuirfidh sé na huimhreacha ar ais mar fhormáid téacs, tá an réamhshocrú bréagach, ionas gur féidir leat é a fhágáil bán.

4. Agus ansin cliceáil OK, baineadh na huimhreacha ón gcill roghnaithe, ansin tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fheidhm seo a chur i bhfeidhm, gheobhaidh tú an toradh seo a leanas:

uimhreacha sliocht doc amháin 8

Cliceáil chun Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Modh 4: Sliocht uimhir deachúil as sreangán téacs leis an bhfoirmle

Má tá na teaghráin téacs lena n-áirítear roinnt uimhreacha deachúlacha i do bhileog oibre, conas nach bhféadfá ach na huimhreacha deachúil a bhaint as na teaghráin téacs?

Is féidir leis an bhfoirmle thíos cabhrú leat na huimhreacha deachúil a bhaint as na teaghráin téacs go tapa agus go héasca.

Iontráil an fhoirmle seo :=LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A5&"1023456789")),999),ROW(INDIRECT("1:999")))), Agus ansin an láimhseáil a líonadh síos go dtí na cealla ar mhaith leat an fhoirmle seo a bheith iontu, baineadh na huimhreacha deachúla go léir as na teaghráin téacs, féach an scáileán:

uimhreacha sliocht doc amháin 13


Sliocht uimhir amháin ó teaghráin le Kutools le haghaidh Excel

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Ailt Choibhneasta:

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (61)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
how to get a extract extact numbers from text
This comment was minimized by the moderator on the site
Hi, I'm looking to list all numbers in a string before a certain character. The string has letters and numbers, so I want to remove the letters and only list the numbers that appear before the second minus sign "-". Example:MUMUT-S941-22460991e002
I want that to isolate the numbers 941 in the above example.
This comment was minimized by the moderator on the site
How to use Index instead of Indirect to extract number from string. Indirect is volatile and Index can replace it as per http://www.excelhero.com/blog/2011/03/the-imposing-index.html. Thanks Ahead!
This comment was minimized by the moderator on the site
Hello! I found the following formula to extract only the numbers from text strings in Excel very useful

SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)

Could you explain more on this formula as it seems quite complicated? Many thanks.
This comment was minimized by the moderator on the site
Reading ID: 3151346 BeatO User ID: 239930 Name: Mahesh Phone: 9823010759 Email: City: nashik State: Maharashtra Reading: 55 Meal Time: Random Meal Type: RANDOM Reading Time: 2020-03-01 00:15:57 View user readings in Portal



I want to extract numeric no. post Reading text
This comment was minimized by the moderator on the site
Hello, how can i extract the cheque no.only from the below text

OUTWARD CLEARING CLRG CHQ DEPOSIT CHQ. NO: 000123~700320456 ABCD ARAB PLAZ BRANC Value DATE, 01/02/2020 - S79519642

i tried below formula and its working but i need to change the 1:57 by calculating how many character before the first digit plus how many digits i want to extract.
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+5 im using this formula to know how many characters before the first number..

=TEXTJOIN("",TRUE,IFERROR(MID(A3,ROW(INDIRECT("1:57")),1)+0,""))
This comment was minimized by the moderator on the site
=Mid(A2,find("CHQ. NO", A2)+9,6)
This comment was minimized by the moderator on the site
Hi. I wonder to know is it possible to extract from the next string '102-105+106-10605-10605 -10631-10632-10633-10634-10635+107' all values of only three digits that have sign '-' (i.e. minus) before them (or plus - doesn't matter). Then extract extract all 5-digits values with the same rule? If this is possible to do I will save many many hours of manual work that I can devote to lot's of uselful tasks.

P.S.
I know that regular expressions are able to do that, BUT... I hate their syntax, so I'm searching any other method to complete this task.

Thanks in advance.
This comment was minimized by the moderator on the site
Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40

Ali Baba Dark Chocolate 25g box 12 pcs
Ali Baba Dark Chocolate 20g*24 box
Cadbury 5 Star White Chocolate 15g
Kinder 2 White Chocolate 25g*24
ALpella Biscuits W/Marshmallow300g
Alpella Chocolate 40g
This comment was minimized by the moderator on the site
did anyone answer or did you figure this out? this is exactly my problem right - even down to the letter 'g'. the problem with the formula I am using now is that it returns the first number, if there are more than one, from the string and not the number I am wishing to return.
This comment was minimized by the moderator on the site
Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40


Ali Baba Dark Chocolate 25g box 12 pcs
Ali Baba Dark Chocolate 20g*24 box
Cadbury 5 Star White Chocolate 15g
Kinder 2 White Chocolate 25g*24
ALpella Biscuits W/Marshmallow300g
Alpella Chocolate 40g
This comment was minimized by the moderator on the site
can any one answer this question, how to extract the no's
This comment was minimized by the moderator on the site
You may try select the number before g in the first cell, copy and paste it in new cell. Then press CTRL + E. All the number before g will be auto generated. 

This comment was minimized by the moderator on the site
Tkssssss you saved my day :) God bless you
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations