Extracting numbers from a cell
 

I have a number entries in text format such as

USD 1,265
USD 4,678
GBP 284
EUR 4,566

How do I extract just the number from these cells so that I can sum
them or use in formulas. i.e. I want to end up with a list that just
reads:

1265
4678
284
4556

Cheers


--
morchard
------------------------------------------------------------------------
morchard's Profile: http://www.excelforum.com/member.php...o&userid=24761
View this thread: http://www.excelforum.com/showthread...hreadid=383451

highlight the one column in which the data are located.
use data-text to columns and use <space as delimiter

morchard wrote in
message ...

I have a number entries in text format such as

USD 1,265
USD 4,678
GBP 284
EUR 4,566

How do I extract just the number from these cells so that I can sum
them or use in formulas. i.e. I want to end up with a list that just
reads:

1265
4678
284
4556

Cheers


--
morchard
------------------------------------------------------------------------
morchard's Profile:
http://www.excelforum.com/member.php...o&userid=24761
View this thread: http://www.excelforum.com/showthread...hreadid=383451

Dear Morchard,

Assuming that the first 3 characters represent currency, please apply
following formula in the cell where you want the values

'=VALUE(MID(B3,4,LEN(B3)-3))

where B3 is the cell which contains USD 1,245 etc. and copy it onwards.

Let me know if it helps.

Rgds
Ajay

"morchard" wrote:


I have a number entries in text format such as

USD 1,265
USD 4,678
GBP 284
EUR 4,566

How do I extract just the number from these cells so that I can sum
them or use in formulas. i.e. I want to end up with a list that just
reads:

1265
4678
284
4556

Cheers


--
morchard
------------------------------------------------------------------------
morchard's Profile: http://www.excelforum.com/member.php...o&userid=24761
View this thread: http://www.excelforum.com/showthread...hreadid=383451