Invisible character

[DJ]

Hi,


I received some data from our business partner which has some employee names and their details. Employee's First name and last name should be seperated by a single space only. However, there are many names which seems have two or more spaces. I checked that actually there is single space only others are some special character which seems like a space.


Kindly help me with removing this special character from this data.


Thanks,

DJ

 

[Faseeh]

Hi Dj,


Try TRIM() to remove extra spaces, can i have sample data from your sheet??


Regards,

 

[DJ]

Hi Faseeh,


As I wrote in my previous post, the spaces I am refering to are not spaces but special charaters. Also, these are not trailing spaces but between the fist name and last name so Trim() will not work.


Thanks,

DJ

 

[Debraj]

Hi DJ,


Assuming your Name field is in A2..


=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(A2),LEN(TRIM(A2))-OR(RIGHT(TRIM(A2))={"?","!","."})),CHAR(160)," ")))


Courtesy : http://www.rondebruin.nl/clean.htm


Regards,

Deb

 

[Faseeh]

@ DJ,


Then i suggest you to try Deb's formula that should work or upload a sample file.


Regards,

Faseeh

 

[DJ]

Hi Debraj/Faseeh,


Thank you so much for the link and formula, it worked perfect. I am very grateful to you as you helped me to save many hours' effort.


Thanks,

DJ

 

[Debraj]

Happy to Help...

 

[shrivallabha]

The characters which seem like "space" character but do not behave like are the ones called non breaking spaces which are used by many programs and html. See the link below:

http://en.wikipedia.org/wiki/Non-breaking_space


Debraj's formula takes care of it using CHAR(160).

 

[Marc L]

Don't forget the other char like #160 :   #255 !  

(in PC, don't know for MAC)