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

Mleeds

New Member
Joined
Mar 20, 2016
Messages
7
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">$1200.00</td><td style="text-align: right;;">1200</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J7</th><td style="text-align:left">=--MID(<font color="Blue">I7, 2, 255</font>)</td></tr></tbody></table></td></tr></table><br />

HTH

Dave
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 20, 2016
Messages
7
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.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">$1200.00</td><td style="text-align: right;;">1200</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J7</th><td style="text-align:left">=--MID(<font color="Blue">I7, 2, 255</font>)</td></tr></tbody></table></td></tr></table><br />

HTH

Dave
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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
AB
1$1.231.23

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

Worksheet Formulas
CellFormula
B1=--MID(A1, 2, 255)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
Did anything I suggested in Message #3 work for you?
 

Mleeds

New Member
Joined
Mar 20, 2016
Messages
7

ADVERTISEMENT

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

New Member
Joined
Mar 20, 2016
Messages
7
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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
AB
1$1.23#VALUE!
2$4.56

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

Worksheet Formulas
CellFormula
B1=SUMPRODUCT(0+A1:A2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,816
Messages
5,598,264
Members
414,221
Latest member
MD1222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top