Currency figure as text so unable to format or use in formulas.

Eamonn100

Board Regular
Joined
Nov 12, 2015
Messages
152
When I copy/paste currency figures from the internet they are entered as text, meaning I can't format them and formulas won't read them. Does anyone know a formula that can help? My example is in dollars but it can have a different currency symbol at times.

e.g. $43.00
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
The quickest way to deal with it would be to select the columns affected and do a find and replace:

Find: $
Replace: (i.e. nothing)
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
Do you mean to include in other formulae?

This will work with single cell references:

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

If this doesn't suit you, then you will need to give us some sample data and formulae in tabular format so that we can better understand the problem.
 
Last edited:

Eamonn100

Board Regular
Joined
Nov 12, 2015
Messages
152

ADVERTISEMENT

I copy/paste it from the internet. Then I reference it to other pages. So yes, I will be including it in other formula.

I can do without the $ sign. It's the figure in need as I can later format it again.
 
Last edited:

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

You could do it with a helper column, or amend your existing formula that reference the data.
 

Eamonn100

Board Regular
Joined
Nov 12, 2015
Messages
152
You could do it with a helper column, or amend your existing formula that reference the data.

Gaz if you think you could amend the existing formula that would be great. It would tidy everything up. I tried AliGW's formula but it only allowed it to be read by formulas. It still couldn't be formatted.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Post your existing formula that references this data, one of us will try and give you a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,833
Members
413,943
Latest member
Dhornsby21

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