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

 

[Eamonn100]

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

	a	b	c	d
1	Prev Close			=INDEX(B:B,MATCH("Prev Close ",$B:$B,0)+1)
2	$43.00
3

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[gaz_chops]

I think Ali that the formula still returns text, if he tries your formula as

=SUBSTITUTE(A19,"$","")+0

 

[AliGW]

Have you tried this?

=--SUBSTITUTE(INDEX(B:B,MATCH("Prev Close ",$B:$B,0)+1),"$","")

 

[Eamonn100]

Please explain exactly what you mean by this:

When I tried to format it into currency it wouldn't do it. I tried to format it as a date and a few other formats but no joy.

 

[AliGW]

I think Ali that the formula still returns text, if he tries your formula as

=SUBSTITUTE(A19,"$","")+0

Or this:

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

 

[AliGW]

When I tried to format it into currency it wouldn't do it. I tried to format it as a date and a few other formats but no joy.

See post #13.

 

[gaz_chops]

If the currency is only 1 character, try

=MID(INDEX(B:B,MATCH("Prev Close ",$B:$B,0)+1),2,99)+0

 

[Eamonn100]

I think Ali that the formula still returns text, if he tries your formula as

=SUBSTITUTE(A19,"$","")+0

Thanks Gaz this worked. Thanks again.

 

[Eamonn100]

Or this:

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

Thanks Ali this worked too.

 

[gaz_chops]

That is ok if only $, if different currency symbols pre-fix then the last solution i posted will work with any 1 character symbol.