| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering out text from numbers
Hi, I have a long column of data, some cells have numbers in the cells some with text. I want to get the numbers out. Any ideas on a formula for this? Thanks Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=570070 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
filtering out text from numbers
You can put this in a helper column and copy down
=IF(ISTEXT(A1),A1,"") Vaya con Dios, Chuck, CABGx3 "simonsmith" wrote: Hi, I have a long column of data, some cells have numbers in the cells some with text. I want to get the numbers out. Any ideas on a formula for this? Thanks Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=570070 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
filtering out text from numbers
Thanks, tried that but some format or somethign is stopping that from differentiating text from the strings of 0001111000 that I want. Have found a long hand way to do it now Cheers Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=570070 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
filtering out text from numbers
It's easy to see why it did not work as desired......0001111000 is not a
number....it is actually a TEXT value just as much as a letter-character string. If all of the values you want to delete start with a leading zero, then this formula should help......... =IF(Left(A1=0,"",A1) Vaya con Dios, Chuck, CABGx3 "simonsmith" wrote in message ... Thanks, tried that but some format or somethign is stopping that from differentiating text from the strings of 0001111000 that I want. Have found a long hand way to do it now Cheers Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=570070 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
filtering out text from numbers
On Wed, 9 Aug 2006 15:07:27 -0400, simonsmith
wrote: Hi, I have a long column of data, some cells have numbers in the cells some with text. I want to get the numbers out. Any ideas on a formula for this? Thanks Simon Do you need to retain the leading zero's? Do you want the string returned as a text string (containing only numbers) or as a number (would have no leading zero's)? If you want to return a numeric value, then: =IF(ISERR(1*A1),"",1*A1) If you want to return a text string that contains only numbers (would include leading zero's), then: =IF(ISERR(1*A1),"",A1) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| converting numbers to text | New Users to Excel | |||
| How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
| How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
| Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Linear Mode
