Processing downloaded data

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?

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

thanks Steve,
the Value function served my purpose.
BC

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> 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(IND
IRECT("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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=556106
>
>

You're welcome.

Cheers,
Steve

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=556106
>

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

Thank you so much, I will try
Bobocat

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=556106
>