Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is ‘ 4 ‘.
My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.
With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))))
it perfectly resolved this challenge as you can see from following screen shot.
Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.