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

[Eamonn100]

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

 

[AliGW]

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)

 

[Eamonn100]

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)

Thanks but I need it with a formula.

 

[AliGW]

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.

 

[Eamonn100]

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.

 

[AliGW]

See above.

 

[gaz_chops]

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

 

[Eamonn100]

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]

Post your existing formula that references this data, one of us will try and give you a solution.

 

[AliGW]

Please explain exactly what you mean by this:

It still couldn't be formatted.