Extract Numbers from alphanumeric strings

[Sandeep Warrier]

Hey All,

I have this formula that extracts numbers from alphanumeric strings.

{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))}

However this extracts only the 1st instance of the numbers

In a string like 123avfbsdf4556.. it'll extract only 123.

My questions are the following:
1. Is there a way that i could get the result as 1234556
2. A way which refers to a cell where I put in a number and it'll extract those many number instances. In the above example, if I put the number as 1, it'll extract 123. If I put the number as 2, it'll extract 4556 and so on.

I guess this would require some modifications to the Match function so that it does not look at only the 1st instance.

Thanks in advance for any help.

Regards,
Sandeep.

 

[hardeep.kanwar]

Neither Formula Nor VBA

Copy The Column which you have data

Open MS Word<Paste<CTRL+A<CTRL+H

Find What type- ^$
Replace With- Leave Blank

Replace All

Copy And then paste to that Particular Column in Excel

Hardeep kanwar

 

[hardeep.kanwar]

Using VBA

Try This userDefined

Function parseInt(text)
Dim i As Long
Dim ch As String
Dim nums As String

nums = ""
For i = 1 To Len(text)
ch = Mid(text, i, 1)
If IsNumeric(ch) Then nums = nums & ch
Next i

parseInt = nums
End Function


put = in Any Cell

Press Shift+F3

Choose from the list i.e parseInt

Hardeep kanwar

 

[Richard Schollar]

Thanks a lot Richard & PGC

PGC, Your code worked perfectly.. Do you have any tutorials on the .Pattern stuff? I've come across it in many codes and seems to be very useful.


Richard, excel didnt allow me to enter your formula. I guess that since I use excel 2003 its too long.

Quite right - I exceeded the nesting limit! Try this variant that works with 2003:

=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1)))+1,ROW(INDIRECT("1:"&LEN(A1)))),1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

Confirmed with Ctrl+Shift+Enter

 

[Sandeep Warrier]

Thanks for the web reference PGC...

 

[jbeaucaire]

Here's another UDF I use for this:

Function CleanAll(ByVal Txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel)
    For X = 1 To Len(Txt)
        If Mid(Txt, X, 1) Like "*[!0-9]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only numbers
'        If Mid(Txt, X, 1) Like "*[!A-Za-z ]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters and spaces
    Next
CleanAll = Replace(Txt, Chr(1), "")
End Function

Used as =Cleanall(A1)

You can change the commented line out and convert it to leaving words and spaces, swap which of the blue lines has the apostrophe.

 

[Sandeep Warrier]

Richard... your formula works perfectly now... thanks...

Hardeep & jbeaucaire... tried out your codes and they seem to work fine... thanks a lot...