pick out only the numbers

[niladri20052006]

Hi All,

I have list consisting of Number and Text. Like below

01234ONTP687
2356TR4892
6758OPTYE5689
9252715678 ext1

How will I get only the numbers? I do not want in the cell.

 

[niladri20052006]

PLEASE IGNORE MY PREVIOUS POST

Hi All,

I have list consisting of Number and Text. Like below

01234ONTP687
2356TR4892
6758OPTYE5689
9252715678 ext1

How will I get only the numbers? I do not want TEXT in the cell.

 

[p45cal]

(You can edit you posts within 10 minutes or so of posting.)
With something like
01234ONTP687
are you wanting one number (01234687)
or two (01234 and 687)?

 

[niladri20052006]

Hi

It is only one number in one cell. But there is text in it like below:

1234OPT567

The number is 1234567

I want to Remove OPT from the cell.

I hope you now can understand what exactly I am trying to get.

Let me know if you have any question(s).

 

[arthurbr]

Cross post http://www.excelforum.com/excel-general/784581-pick-out-only-the-numbers.html

 

[p45cal]

A user defined function will be the cleanest way to do this; see if this fits the bill:

Excel Workbook
	A	B
10	01234ONTP687	01234687
11	2356TR4892	23564892
12	6758OPTYE5689	67585689
13	9252715678 ext1	92527156781
14	1.234OPT567	1234567
Sheet

Formula in B10 copied down, supported by the code:

Function NumericOnly(txt As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9]"
    .Global = True
    NumericOnly = Trim(.Replace(txt, ""))
End With
End Function

 

[anandvarma]

try this

Excel Workbook
	A	B
1	01234ONTP687	01234687
2	2356TR4892	23564892
3	6758OPTYE5689	67585689
4	9252715678 ext1	9252715678 1
Sheet5

you need to download Morefunc

 

[niladri20052006]

Hi All,

Thanks for your response...

The code is working fine but the formula is not working in my systems.

Thanks a lot to all of you

 

[pgc01]

...
    NumericOnly = Trim(.Replace(txt, ""))
...

Hi p45cal

The Trim() seems not to be necessary?

 

[p45cal]

Hi p45cal

The Trim() seems not to be necessary?

Quite! (oops!)