Quick Number Reformatting

BlueDevil12

New Member
Joined
Apr 22, 2021
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hello, I’m trying to reformat numbers so they can be used in a SUM formula. My source data has to be copied and pasted into excel. It often contains $ and , which messes up the formula (ex $1,400). It doesn’t change if I update the cell format. Is there a way to quickly get rid of the extra characters so just the number remains?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,599
Office Version
  1. 2016
Platform
  1. Windows
Can you use formula like this?
=SUBSTITUTE(A1,"$","")

may not change to number. Try this perhaps
=VALUE(SUBSTITUTE(D4,"$",""))
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
hi and welcome to MrExcel.
if you are able to create a new column to help you can use a sheet formula like
Excel Formula:
=VALUE(SUBSTITUTE(SUBSTITUE(A1, "$",""),",", ""))
assume data is in col A
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try this...
1) Select the entire column with those values in it
2) Format the cells as "General" if it is not already that setting
3) Open the Text To Columns dialog box (Data tab, Data Tools panel)
4) Select Delimited and click Next
5) Make sure the Comma checkbox is NOT checked
6) Click the Finish Button
7) Format the cells as "General" (again) to change from the Currency format it just became
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,599
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this...
1) Select the entire column with those values in it
2) Format the cells as "General" if it is not already that setting
3) Open the Text To Columns dialog box (Data tab, Data Tools panel)
4) Select Delimited and click Next
5) Make sure the Comma checkbox is NOT checked
6) Click the Finish Button
7) Format the cells as "General" (again) to change from the Currency format it just became
I guess you need to tick Other and put $ sign, right?

Learn new trick today
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
You can leave the $ sign in there if that is your currency symbol and turning the cell to General at the end will remove it.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,599
Office Version
  1. 2016
Platform
  1. Windows
You can leave the $ sign in there if that is your currency symbol and turning the cell to General at the end will remove it.
I see. The reason it did not work for me was because my default currency symbol is not $.
Thanks
 

Forum statistics

Threads
1,141,487
Messages
5,706,663
Members
421,459
Latest member
Taamrak

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