isolate numbers in a cell

  • Thread starter Thread starter jeremy via OfficeKB.com
  • Start date Start date
J

jeremy via OfficeKB.com

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
 
Are the characters and numbers in a specific format, or are they anywhere in
the cell?
 
The numbers are all sequential. The text/puctuation are before or after....
No pattern other than that...
 
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,

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.
 
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
 
Copy the formula I gave you into a text editor like notepad, do edit>replace
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
edit>paste special as values in place. Now you can replace the original data
with the extracted numbers


regards,

Peo Sjoblom
 
BenjieLop

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

Sure I'm interested in the formula for isolating unmbers from
alphanumerics....
 
<< 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. Thi
will work for you.

Regards
 
Back
Top