isolate numbers in a cell
 

I need to eliminate all the characters in a cell that aren't numbers
(letters and punctuation).
(And put in a helper column or before an IF statement.)

Thanks.

Jeremy

--
Message posted via http://www.officekb.com

Are the characters and numbers in a specific format, or are they anywhere in
the cell?

"jeremy via OfficeKB.com" wrote:


I need to eliminate all the characters in a cell that aren't numbers
(letters and punctuation).
(And put in a helper column or before an IF statement.)

Thanks.

Jeremy

--
Message posted via http://www.officekb.com

The numbers are all sequential. The text/puctuation are before or after....
No pattern other than that...

--
Message posted via http://www.officekb.com

As long as the the numbers are grouped this will work

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter
assuming that the first data starts in A1

if you have leading zeros remove the first 2 --


Regards,

Peo Sjoblom

"jeremy via OfficeKB.com" wrote:

The numbers are all sequential. The text/puctuation are before or after....
No pattern other than that...

--
Message posted via http://www.officekb.com

Jeremy,

I am not sure if you are now able to read my post in this NG (as you
are signed in as a Guest) but should you be able to, please post back
and I will have a formula that will extract numbers from
alphanumerics.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=377416

PEO

I'm new to excel--shift control enter ??

I severalspread sheets with a list of numbers

4000
4909AD
NHG
MJH0044
6787
"7878

I'm hoping to paste them into a column and then eliminate the letters and
puctuation, so that I have a range of numbers 0 to 9999. The text entries
can be zero....

Thanks

--
Message posted via http://www.officekb.com

Copy the formula I gave you into a text editor like notepad, do editreplace
and replace A1 with the cell reference where your data starts, so assume the
data starts in D4 replace A1 with D4, now copy the formula from notepad to
the formula bar in excel (or doubleclick a cell and paste it there), then
finish with ctrl + shift & enter and if done correctly you should get the
formula enclosed in curly brackets (so called array formula) like {formula}
Once done drag the formula cells fill handle to copy it to cover all the
cells necessary, hight light all cells you created qand copy them, do
editpaste special as values in place. Now you can replace the original data
with the extracted numbers


regards,

Peo Sjoblom

"jeremy via OfficeKB.com" wrote:

PEO

I'm new to excel--shift control enter ??

I severalspread sheets with a list of numbers

4000
4909AD
NHG
MJH0044
6787
"7878

I'm hoping to paste them into a column and then eliminate the letters and
puctuation, so that I have a range of numbers 0 to 9999. The text entries
can be zero....

Thanks

--
Message posted via http://www.officekb.com

PEO

Thanks! That worked and saved me a lot of trouble....

jeremy

--
Message posted via http://www.officekb.com

BenjieLop

I'm a guest--how do i sign in?

Sure I'm interested in the formula for isolating unmbers from
alphanumerics....

--
Message posted via http://www.officekb.com

<< BenjieLop

I'm a guest--how do i sign in?

You have to register with excelforum.com.



<< Sure I'm interested in the formula for isolating unmbers from
alphanumerics.

The formula that I have is the same as what Peo Sjoblom posted. This
will work for you.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=377416

My pleasure, thanks for the feedback

Peo

"jeremy via OfficeKB.com" wrote:

PEO

Thanks! That worked and saved me a lot of trouble....

jeremy

--
Message posted via http://www.officekb.com