I am not able to trim the data with =trim(a1) function please help, Sachin

[ThrottleWorks]

Hi


I have a data, this data has lot of space in the cells.

I am using trim function to remove the spaces, but this function is not working for me.


The spaces are to the left & right of the cell value not in the middle.

I verified the space count by using LEN function, do not know why trim is not working.


example of value


space space space sachin space space space


the trim function is giving me value with space only,


kindly help.

 

[Luke M]

Sachin,

Perhaps they are not true spaces, but some other non-printable character? You might be able to use a combination of TRIM and CLEAN, like

=TRIM(CLEAN(A2))

to get it to work, unless they are character 160. Character 160, for some reason, is not removed by the clean function. You have to do a find and replace (or use VB) to remove those.

 

[ThrottleWorks]

Hi Luke Sir, thanks a lot for your help & valuable time,


I am trying this, will get back to you soon.