Extract numbers from cells containing mixed alpha-numeric strings

Question

I have one column that looks like this:

      A
1 om2222prakash
2 kumar83566
3 222gsrana
4 k4566hhhh23

How can I find out only numeric data in A1, A2, A3 and A4? I am using Microsoft Excel 2007, and would like to have the output look like this:

      A
1 2222
2 83566
3 222
4 456623

Answer 1

Well, open VBE (Visual Basic Editor) with ALT+F11. In the VBA Project Explorer right click and add a Module.

Copy and paste the below code and hit F5 to run the macro.

Option Explicit

Sub GetNumbers()

    Dim uColumn As String

    ' if your data is in a different column then change A to some other letter(s)
    uColumn = "A"

    Dim i As Long, j As Long, r As Range
    For i = 1 To Range(uColumn & Rows.Count).End(xlUp).Row
        Set r = Range(uColumn & i)
        Dim tmpStr As String
        tmpStr = vbNullString
        For j = 1 To Len(r)
            If IsNumeric(Right(Left(r, j), 1)) Then tmpStr = tmpStr & Right(Left(r, j), 1)
        Next j
        r.NumberFormat = "@"
        r = tmpStr
    Next i

End Sub

There's a variable named uColumn. It has A currently assigned as the column. If your data sits in a different column then change from A to your column letter(s)

Hope this helps

Answer 2

Here is a formula that should work.

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

The reference in ROW() needs to be altered to include all the rows of data you would like considered (referenced twice in the formula). So for example, if your last row of data is 200 it would read ROW($1:$2000).

If your alpha numeric data is in A1 then place this formula into B1 and hit shift+enter (this is an array formula).
This will take only the numbers from A1 and place them in B1. Drag the corner of B1 down to the bottom of your data to auto fill for the rest of your data.

You should see your alpha numeric data in Column A and only the numbers from that data in column B allowing you to do as you wish with them.

Answer 3

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

Replaces specified characters in a string with alternate characters. In the example numeric are replaced with blanks, it should work with other things like special characters.

Answer 4

Following the substitute logic, you would use the following to remove letters and symbols. If I missed any, all you need is a quick modification following the same pattern.

You will need to change the starting cell (it's A2 in the sample below below).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"A",),"B",),"C",),"D",),"E",),"F",),"G",),"H",),"I",),"J",),"K",),"L",),"M",),"N",),"O",),"P",),"Q",),"R",),"S",),"T",),"U",),"V",),"W",),"X",),"Y",),"Z",)," ",),".",),"(",),")",),"-",),"+",),"=",),"!",),"@",),"#",),"$",),"%",),"^",),"&",),"*",),"_",),"[",),"]",),"{",),"}",),"\",),"|",),":",),";",),"""",),"<",),">",),",",),"?",)