Capture Only Numbers in a string

**bconner** · 11-19-2008, 02:11 PM

Is there a function in Excel that will allow me to only capture the numbers in a string? Below is a list and I would like to in a seperate column only capture the numbers in each of the below strings. I can't seem to figure out how to get rid of the *

HB 8185******
HB 8214******
HB 8216
HB 8217******
HB 8220******

**martindwilson** · 11-19-2008, 02:53 PM

use find replace
prefix the * with tilde ~
so find
~*
replace with nothing

**bconner** · 11-19-2008, 03:25 PM

There maybe times when I have hundreds or even thousands of lines of data how can I add the prefix ~ between the number and * easily?

Thanks

**arthurbr** · 11-19-2008, 03:31 PM

If the format is always the same try Data - Text to columns - Fixed width insert delimiters OK

**JBeaucaire** · 11-19-2008, 03:36 PM

I think you may need to add in a User Defined Function to do this simply. This page explains how to do it fully:
http://www.ozgrid.com/VBA/ExtractNum.htm

Once you've installed the User Defined Function ExtractNumber, then this formula would be all you need if the data were in column A starting at A1:

Please Login or Register  to view this content.

Remember, this won't work until YOU install this new function following the instructions on that link.

Good luck.

**StephenR** · 11-19-2008, 03:42 PM

Another approach in case you're bored:

Please Login or Register  to view this content.

**martindwilson** · 11-19-2008, 07:12 PM

i'm confused!!! select column(s) or whole work sheet with your data in
and use find /replace as i said . that will delete all * from your data
or were you using * as an example of stuff you didnt want?
of course you can use text to columns to seperate ***** from rest but what is the point?

**Ron Coderre** · 11-19-2008, 07:30 PM

With
A1 containing a string with consecutive numbers....eg HB 8185******

This formula pulls only the consecutive numbers from that cell

Please Login or Register  to view this content.

In the above example, the formula returns: 8185

Is that something you can work with?

**martindwilson** · 11-20-2008, 07:36 AM

i am unconfused!
you dont have to put ~ in front of the * on your worksheet.
you only use it in the find screen the ~ makes the find look for *
if it was ommited and you just did find * /replace nothing it would treat the *as a wild card and delete everything
just do as in attached image and hit replace all