Remove blank space in front of text to turn into a number. Trim, Substitute will not work.

[jaboomgaarden]

I cannot figure out how to remove one blank space in front of a text in column D to turn it into a number.

This seems like it should be simple but i've tried looking up different tricks, none of them seem to work. Find/Replace, Trim, substitute won't work.

Cell D2
"*225.00"

I'm out of ideas.

[TMS]

Probably a non breaking space rather than just a "simple" space. Select and copy one of the characters and then use that in your global replace.

[jtakw]

Hi,

Try:

=SUBSTITUTE(D2,CHAR(160),"")+0

Remove the +0 at the end of the formula if you Don't want the result converted to Real Number.

[CAABYYC]

=substitute(d2,char(code(left(d2,1))),)-0

[jaboomgaarden]

=IFERROR(SUBSTITUTE(D3, CHAR(160),"")+0,"")

This worked ^^^

You are all my favorite

[jtakw]

Glad you got it sorted.

Add rep would be appreciated, click the "Star" a the lower left corner of the posts you found helpful.

If your issue is "Solved", please mark the thread as such, Thank You.