Share via

Extract numbers out of text?

Anonymous
2010-07-17T04:03:59+00:00

Dear all,

I have the below values, that has numbers along with text. I only want to extract numbers. If an entry has more than one number, it should combine all the numbers and result the output separated by comma and without any extended space.

A1-:

TSBU/UCP/INDEX-4586886668

Virtual-Office-list-External12666586

Smart Shop.Validated-Expenses(1566558)

12455598/smart.shop.validated

Sufi_786_Accline_Quality-review

Daily_04565898**Mis_tracker

56511*1256_Quality_review

09915quality_offshore_business

Offshore quality numbers 1588888881255698699989 reviewed

****Values778*256///5686sufi*125

Conditional***1235data-45896

////Onshore_Mis_Tracker**-15699//*~SUFI

I want the values in B1 as -:

4586886668

12666586

1566558

12455598

786

04565898

56511,1256

09915

1588888881255698699989

778,256,5686,125

1235,45896

15699

Thanx very much in advance!

Regards

Dharmesh

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2010-07-17T05:21:44+00:00

You are going to need VB code to do that. Here is a function that you can use in your own code OR as a UDF (user defined function)...

Function GetNumbers(ByVal S As String) As String

  Dim X As Long

  For X = 1 To Len(S)

    If Mid(S, X, 1) Like "[!0-9" & Chr$(10) & "]*" Then Mid(S, X, 1) = " "

  Next

  GetNumbers = Replace(Replace(Replace(WorksheetFunction.Trim(S), " " & vbLf, vbLf), vbLf & " ", vbLf), " ", ",")

  Do While InStr(GetNumbers, vbLf & vbLf)

    GetNumbers = Replace(GetNumbers, vbLf & vbLf, vbLf)

  Loop

End Function

If you use it within your own code, you would call it like this...

Range("B1").Value = GetNumbers(Range("A1"))

If you use it as a UDF (that is, a formula located directly on your worksheet), then you would put this formula in B1...

=GetNumbers(A1)

To install the function for either type of usage, press Alt+F11 from any worksheet to go into the VB editor, then click Insert/Module from the VB editor's menu bar, and then copy/paste the above function into the code window that opened up.

Was this answer helpful?

0 comments

22 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-07-17T09:56:54+00:00

    So far, I can only extract entries with one number, but not entries with multiple numbers, using only formulas. Basically, I substitute all numeric characters with a special character (in this case "@") and I use that to figure out the location of the number in the string. Hopefully, this gives you some ideas.

    Put this formula in B1:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "0", "@"), "1", "@"), "2", "@"), "3", "@"), "4", "@"), "5", "@"), "6", "@"), "7", "@"), "8", "@"), "9", "@")

    Put this formula in C1:

    =MID(A1,SEARCH("@",B1),LEN(B1)-LEN(SUBSTITUTE(B1,"@","")))

    Wait, are all those lines in A1? Ok, this won't work.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-07-17T09:23:10+00:00

    You can "execute" the GetNumbers() macro proposed by Rick just by using formulas, like this:

    Assuming your value is in cell A1, put the following formula in cell B1:

    =IF(NOT(OR(COLUMN()-1=10,AND(COLUMN()-1>=CODE("0"),COLUMN()-1<=CODE("9")))),SUBSTITUTE(A1,CHAR(COLUMN()-1)," "),A1)

    Copy cell B1 to the right all the way to cell IV1.

    For each new cell all occurences of one specific character are substituted with blanks. Linefeed and digits are not substituted.

    In cell IW1 put: =TRIM(IV1)

    In cell IX1 put: =SUBSTITUTE(IW1," "&CHAR(10),CHAR(10))

    In cell IY1 put: =SUBSTITUTE(IX1,CHAR(10)&" ",CHAR(10))

    In cell IZ1 put: =SUBSTITUTE(IY1," ",",")

    In cell JA1 put: =SUBSTITUTE(IZ1,CHAR(10)&CHAR(10),CHAR(10))

    In cell JB1 put: =IF(LEN(JA1)=LEN(IZ1),"",SUBSTITUTE(CHAR(10)&CHAR(10),CHAR(10),JA1))

    Copy cell JB1 to the right until you get a blank cell as the result.

    You numbers, separated by commas, are found in the rightmost non-blank cell in row 1. It will contain the same as if GetNumbers(A1) had been used.

    Hope this helps / Lars-Åke

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-07-17T08:20:04+00:00

    No, you have too many unmanageable conditions... multi-line text, very long digit runs, arbitrary positioning of the numbers within the individual lines, and the need to put commas between the individual numbers located on the same line.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-07-17T08:08:11+00:00

    Thanks Rick for writing a macro for my query but can it not be done without using macros since, I have don't have admin rights to use that in my Office. If you can devise something apart from macros, I would be highly grateful to you.

    Thanks

    Dharmesh

    Was this answer helpful?

    0 comments