# Quick Number Reformatting

#### BlueDevil12

##### New Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Zot

##### Well-known Member
Can you use formula like this?
=SUBSTITUTE(A1,"\$","")

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

#### diddi

##### Well-known Member
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
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

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
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
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

Replies
3
Views
770
Replies
5
Views
153
Replies
6
Views
142
Replies
4
Views
136
Replies
1
Views
30

1,136,732
Messages
5,677,439
Members
419,693
Latest member
divtjd

### 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.

### Which adblocker are you using?

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

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