Removing Non-Numeric Characters - Automatically - How?

[emerald]

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?

[vezerid]

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

[Ron Coderre]

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?
>
>

[emerald]

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 :-)

[vezerid]

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

[Toppers]

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 :-)
>
>

[Sloth]

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 :-)
>
>