Processing downloaded data

B

Brickcounter

I'm downloading share prices form a web page to excel 97, and I get number &
text in a single cell. e.g. 343p.
Is there a formula to remove the text so I can use the resulting number in
further calcs?
 
S

SteveG

Brickcounter,

If you have multiple text characters and they always appear at the end
of the string you can use this array formula.

=VALUE(LEFT(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:10000")),1)),ROW(INDIRECT("1:10000"))))))

Commit with Ctrl-Shift-Enter not just Enter.

If the data always ends in a "p" then you can use this formula:

=VALUE(SUBSTITUTE(A1,"p",""))

OR

Use Text to Columns using the Other delimiter option of "p".

HTH

Steve
 
B

Bobocat

Hi Steven,

In your formula, I found that 2 minus sign before MID, what is the meaning
of --MID?
Moreover, can I use row(1:10000) to replace row(indirect("1:10000")?

Now I am trying to extract the text character from the cell, sometimes at
the front or sometimes at the end

Bobocat
 
S

SteveG

Bobocat,

There is probably a less cumbersome formula but this array formula
should work for you.

=VALUE(MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000))),MAX(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000)))-MIN(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000)))+1))

Commit with Ctrl-Shift-Enter.

The "--" is a double unary operator. It coerces excel to recognize the
numbers stored as text as numbers. I tested without the indirect
function and it appears to work fine.

HTH

Steve
 
Top