How to convert text '$1,200.01' to numeric value '1200.01

[Mleeds]

I have a whole column with hundreds of numbers in that text format which I need to sum but I can't because it's in text format. So how do I convert it to numeric values. Thanks.

 

[dave3009]

Hi Mleeds, welcome to the MrExcel forum.

You could try coercing the text to values. Assuming there is only a single currency symbol then this approach may work.

Excel 2007
	I	J
7	$1200.00	1200
Sheet3

Cell Formulas
Range	Formula
J7	=--MID(I7, 2, 255)

HTH

Dave

 

[Rick Rothstein]

I have a whole column with hundreds of numbers in that text format which I need to sum but I can't because it's in text format. So how do I convert it to numeric values. Thanks.

Select the column, call up the Text To Columns dialog box (Data tab, Data Tools panel, Text To Columns button) and as soon as the dialog box appear, click the Finish button.

As a side note, you can sum the column of text numbers without converting them to real numbers. Let's say your text numbers are in cells A1:A200, you can get their sum with this formula...

=SUMPRODUCT(0+A1:A200)

 

[Mleeds]

I'm using Excel 2013.

What does the '--' double minus in front of the function do?
Also, to make it work I had to change the formula requested to =--mid(a1, 1, actual length of character field in a1, ie. $1.23 is =--mid(a1,1, 5). Using =--mid(a1, 2, 255) as you suggested resulted in #VALUE and does not work. Thank you.

Hi Mleeds, welcome to the MrExcel forum.

You could try coercing the text to values. Assuming there is only a single currency symbol then this approach may work.

Excel 2007
	I	J
7	$1200.00	1200
Sheet3

Cell Formulas
Range	Formula
J7	=--MID(I7, 2, 255)

HTH

Dave

 

[dave3009]

Hi

In this example the double negative coerces text to a value, you can also +0 as in Ricks SUMPRODUCT or *1.

If you start at character 1 in your string you will return $1.23, which cannot be coerced using that method, you need to start at position 2. There then lies the issue of negative numbers so perhaps you'd need to use 3 etc. If you need to sum a range then I would suggest going with Ricks option.

Here is an example based on your data in your last post

Excel 2007

	A	B
1	$1.23	1.23

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =--MID(A1, 2, 255) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Did anything I suggested in Message #3 work for you?

 

[Mleeds]

Hi Rick, it did not. I wish I could but I don't know how to post a snapshot of part of my screen in Excel 2013 to show you.

As an example, in cell A1 and A2, I have:
$1.23
$4.56
=sumproduct(0+a1:a2), results in #VALUE error in A3, note that the values in A1 and A2 are left aligned because they are character values. Thanks for the quick replies.

 

[Mleeds]

Dave3009, it did not work for me because there is a complication and I think I know what the problem is. The text characters in the Excel field were cut n' pasted from an online source, a bank statement on a website (presumably HTML). So while it is non-numeric when pasted into an Excel chart, it's actually also a non-character (maybe it's in HTML?) which is why your formula did not work. On the other hand, it works when I enter the value `$1.23 in Excel (note the back apostrophe).

 

[Rick Rothstein]

Hi Rick, it did not. I wish I could but I don't know how to post a snapshot of part of my screen in Excel 2013 to show you.

As an example, in cell A1 and A2, I have:
$1.23
$4.56
=sumproduct(0+a1:a2), results in #VALUE error in A3, note that the values in A1 and A2 are left aligned because they are character values.

It works for me (which is why I posted it). Did you, perhaps, get your values by copy/pasting them in from the web? Playing a hunch, try this...

=SUMPRODUCT(0+SUBSTITUTE(A1:A2,CHAR(160),""))

 

[dave3009]

Hi

If you look at the links in the signature blocks you will find tools for posting your sheet.

But you're correct, after I copied that data the SUMPRODUCT doesn't appear to work

Excel 2007

	A	B
1	$1.23	#VALUE!
2	$4.56

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =SUMPRODUCT(0+A1:A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>