Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
I am grabbing web query data to a sheet, but it comes in the form
212 p 954 p 25.25 p 555.00 p etc. How can I automate the removal of the non numeric data? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
If your data always start with the number and there is no chance there
will be a second number in the string, then the following *array* formula will extract the number in another column: =VALUE(LEFT(A2,MAX(ROW(1:100)*ISNUMBER(VALUE(LEFT( A2,ROW(1:100))))))) Array formulas must be committed with Shift+Ctrl+Enter. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
If you only need to remove the right side of the cell contents, beginning
with the space, try this: Select your range <Edit<Replace Find what: * (note: that is a space and an asterisk) Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP-Pro "emerald" wrote: I am grabbing web query data to a sheet, but it comes in the form 212 p 954 p 25.25 p 555.00 p etc. How can I automate the removal of the non numeric data? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
Kostis
I get "A value used in the formula is of the wrong data type." Ron <<Does that help? Not really - it misses the 'Automatically' bit :-) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
What can I say... This formula will produce #VALUE! only if the cell is
empty or does not start with a number. Are you sure you are using it properly? In my formula, replace A2 with whatever cell reference contains the number/text (2 replacements). If you are guaranteed that there will be a space after the number, you can try this simpler formula instead: =LEFT(A2, FIND(" ",A2)-1) This formula will also produce #VALUE! if no space is found in the text or in any of the above conditions. This one is NOT an array formula (simply press Enter). If used properly, the first version is more powerful (allows for no space). Does this help? Kostis |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Non-Numeric Characters - Automatically - How?
macrof of Ron's solution:
Range("D1:D4").Replace What:=" *", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Just change range "emerald" wrote: Kostis I get "A value used in the formula is of the wrong data type." Ron <<Does that help? Not really - it misses the 'Automatically' bit :-) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data-Text to Columns
How much more automatic can you expect? It takes about 5 seconds to do an
entire list this way. There are no formulas, no copying and pasting values. Find and Replace is usually the desired way to go, if you can do it. Data text to columns will also take apart the cells. Just make sure there is nothing to the right of the cells, and then select Data-Text to columns. Then select delimited and choose "space" as your delimiter. After it's done, just delete all the columns except the first one. If you paste as text after you have done this once on an open document, it will split the cells automatically. You still have to delete the cells containing text though. "emerald" wrote: Kostis I get "A value used in the formula is of the wrong data type." Ron <<Does that help? Not really - it misses the 'Automatically' bit :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Can I shut off Excel automatically removing the first 0 | Excel Discussion (Misc queries) | |||
removing pre-set characters from comments | Excel Worksheet Functions | |||
Removing Non-Numeric Characters | Excel Discussion (Misc queries) |