Not able to replace the space in the Excel cell using FIND AND REPLACE

[Adarsh S]

Hi, I have copied some data from a *.TXT file and pasted it to excel file. Then I split the data in the cell using TEXT TO COLUMNS. In one particular column there is space in each cell. If I apply some formula it is showing #value!.

The space can be removed manually by removing in each cell. But cannot be removed using FIND AND REPLACE for the whole column.

I have attached the excel for your reference.

My question is how to remove this space and continue with my calculation?

 

[bosco_yip]

Try,

In C3, enter formula and using "double click" to copy down:

=SUBSTITUTE(B3,CHAR(9),"")/60

Regards

 

[Adarsh S]

Thank you very much Sir for your valuable inputs. Yes it is working.

 

[INCOMETAX111]

OR USE THIS

=NUMBERVALUE(B3)/60

 

[Adarsh S]

Thank you very much Sir for your valuable inputs. Yes it is working.

 

[bluesky63]

or use Clean(B3)/60, using can kill most of the hidden hardcore machine code where Trim() cannot remove off, or SUBSTITUTE(B3,CHAR(32),"")/60 or SUBSTITUTE(B3,CHAR(160),"")/60

NUMBERVALUE(B3)/60 is not available in earliest Excel version ?

Thank you !

 

[Adarsh S]

Thank you very much Sir for the details. I was not aware about the functions in the updated version. I tried the other functions, it is working good.

 

[Adarsh S]

Thank you Hany Ali Sir. Much appreciated.