Processing downloaded data

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

 

[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

 

[Brickcounter]

thanks Steve,
the Value function served my purpose.
BC

 

[SteveG]

You're welcome.

Cheers,
Stev

 

[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

 

[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

 

[Bobocat]

Thank you so much, I will try
Bobocat