Quick Number Reformatting

[BlueDevil12]

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?

 

[Zot]

Can you use formula like this?
=SUBSTITUTE(A1,"$","")

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

 

[diddi]

hi and welcome to MrExcel.
if you are able to create a new column to help you can use a sheet formula like

=VALUE(SUBSTITUTE(SUBSTITUE(A1, "$",""),",", ""))

assume data is in col A

 

[Rick Rothstein]

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]

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]

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]

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