pick out only the numbers

N

niladri20052006

Board Regular

Joined

Dec 3, 2010

Messages

121

• Jul 18, 2011

• #2

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.

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Jul 18, 2011

• #3

(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)?

 

Upvote 0

N

niladri20052006

Board Regular

Joined

Dec 3, 2010

Messages

121

• Jul 18, 2011

• #4

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).

 

Upvote 0

arthurbr

Well-known Member

Joined

Dec 8, 2006

Messages

2,725

Office Version

1. 365

Platform

1. Windows

• Jul 18, 2011

• #5

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

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Jul 18, 2011

• #6

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:

Code:

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

 

Upvote 0

A

anandvarma

Active Member

Joined

Apr 26, 2011

Messages

384

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jul 18, 2011

• #7

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

 

Upvote 0

N

niladri20052006

Board Regular

Joined

Dec 3, 2010

Messages

121

• Jul 18, 2011

• #8

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

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Jul 18, 2011

• #9

p45cal said:

Code:

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

Click to expand...

Hi p45cal

The Trim() seems not to be necessary?

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Jul 18, 2011

• #10

pgc01 said:

Hi p45cal

The Trim() seems not to be necessary?

Click to expand...

Quite! (oops!)

 

Upvote 0