Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[Brusky]

ok - I just try #37 and was able to make it work. Probably input error that it did not work the first time. Thank you for the suggestion.

 

[yesterdays]

The only problem with that Formula in #37 is it will return a number, so any leading zero will be by pass

 

[oddchecker]

Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

[TABLE="width: 247"]
<colgroup><col width="247"></colgroup><tbody>[TR]
[TD="width: 247"]E.g. Flamengo (2/13) to only 2/13

Any idea?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

[TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247"]E.g. Flamengo (2/13) to only 2/13

Any idea? [/TD]
[/TR]
</tbody>[/TABLE]

Unfortunately one example is usually not enough to decide on a course of action...

Is the number always at the end of the text as shown in your example? If not, can other numbers (digits) be located after it?

Is the number always surrounded by parentheses? If so, will those parentheses always be the only parentheses in the text?

Can there be any other numbers (digits) located before the number you want?

 

[jamilm]

Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

[TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247"]E.g. Flamengo (2/13) to only 2/13

Any idea?[/TD]
[/TR]
</tbody>[/TABLE]

use this UDF

Function RemoveApha(cellInput) As String           
    Set cellInput = Intersect(cellInput.Parent.UsedRange, cellInput)
    
    For i = 1 To Len(cellInput)
        Select Case Mid(cellInput, i, 1)
            Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/"
                Charval = Mid(cellInput, i, 1)
            Case Else
                Charval = ""
        End Select
    RemoveApha = RemoveApha & Charval
    Next i
        
End Function

 

[Rick Rothstein]

use this UDF

Try your UDF with this text "a1b2c3d(2/3)e4" and you will see why I asked the questions that I did in Message #44.

 

[jamilm]

Try your UDF with this text "a1b2c3d(2/3)e4" and you will see why I asked the questions that I did in Message #44.

Hi Rick,

i used the UDF and i got [TABLE="width: 64"]
<tbody>[TR]
[TD]1232/34[/TD]
[/TR]
</tbody>[/TABLE]

i thought the oddchecker is looking for to remove all normal alpha char and then retain numbers and that special chars.

or perhaps i misunderstood.

 

[Rick Rothstein]

Hi Rick,

i used the UDF and i got [TABLE="width: 64"]
<tbody>[TR]
[TD]1232/34[/TD]
[/TR]
</tbody>[/TABLE]

i thought the oddchecker is looking for to remove all normal alpha char and then retain numbers and that special chars.

or perhaps i misunderstood.

I got the impression he was looking for a specific number. Because of our different interpretations is why I said "Unfortunately one example is usually not enough to decide on a course of action..." The question comes up, though... if the OP really wanted numbers only, then why keep the slash? What about decimal points, thousands separators, plus minus signs, currency symbols, etc. Hopefully the OP will be along shortly to clarify what his question actually is.

 

[ravinderozha]

Thanks Seniors for your great formulas.

I am looking for if this brain storming can provide me an answer for this 'text string' as well.

Fail David Simon; if achieved of marks 560: St. John College _______I want 560 in B1

Pass Julie D'souza of high school; if got marks 75: Michigan College_______I want 75 in B2


Regards
Ravinder Ozha, PhD

 

[ukmikeb]

@ravinderozha

The formula in post #37 works fine.