Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
The numbers are all sequential. The text/puctuation are before or after....
No pattern other than that... -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
PEO
Thanks! That worked and saved me a lot of trouble.... jeremy -- Message posted via http://www.officekb.com |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
<< 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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format a cell for displaying numbers in lakhs | Excel Discussion (Misc queries) | |||
Sum numbers based on the contents of another cell | Excel Worksheet Functions | |||
Is there a cell format for HEX numbers? | Excel Discussion (Misc queries) | |||
occurance of numbers in cell range | Excel Worksheet Functions | |||
create a cell that accumulates, numbers from another cell... | Excel Discussion (Misc queries) |