I want to remove leading spaces in a numeric cell in MS Excel e.g i have a value {' 169,207.00} which i want to convert into numeric as {169,207.00}, Please guide me which excel formula is helpful for me.
4 Answers
PeterH's answer of =TRIM(A1)+0 should have worked.
A couple other variations that should work are
=A1+0=VALUE(A1)
You can use TRIM for this. I.E =TRIM(A1)
TRIM will remove all trailing and leading spaces from a text value.
More detail can be found here:
To turn a text value into numeric you can simply add a 0.
I.E =TRIM(A1)+0 will turn ' 169,207.00 into 169207, you can then format to your desired output.
see formula i used as you used in your formula:
You can also use the formula below:
=SUBSTITUTE(A1," ","")
