Excel 2010 - $123 to Number

[Andrzejek]

I have some data comming in as text:

[pre]
I J
$2,043 $2,090
$4,167 $4,201
$0 $0
$3,000 $3,031
$350 $350
$0 $0
$0 $2,800
$0 $0
$2,014 $0
$250 $0
$3,750 $3,961
[/pre]

Since this is text (and not a number formatted as Currency), I cannot SUM it up.

Is there any (easy?) way in Excel 2010 to convert this text to numbers, so I can format it as Currency and be able to do calculations?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Hi,

"Data coming in as text"

How is that happening. Surely you are IMPORTING this data and not opening it directly.

 

[zelgar]

You can use the =VALUE command or you can Copy/Paste Special/Add to convert the text to a value

 

[Andrzejek]

Yes, you are right.
I am getting this data from DB and it is used to populate a grid (MSHFlexGrid) in my application (in VB6). The values are formatted as text to display correctly in the grid. As a side ‘use’ of this recordset, I dump this data into Excel as a report.

I could ask for the same data again for my report, and just ask for values, and format it as Currency in Excel. But I thought I give it a shot and see if I can find some work around it and re-use my recordset.

Now that I think about it, it occurs to me: I should just create ‘un-formatted’ recordset which I can use in Excel, and format couple of columns in the grid itself to display it as Currency.

Thanks Skip


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[xlhelp]

Type a 1 in any empty cell. Copy it. Then highlight your whole data range. Paste Special and choose Multiply.


Never miss an opportunity to shut up