Formula for removing space or unknown character. (Trim is not working)

Hi There,

I am trying to use the formula “=Trim()” to remove space or unknown character, but this formula is not working. Some body can share the formula to fix this.

Example Sheet Attached. (Name Michale*Arun Kumar, there is unknown space or character between Micale (Space) Arun.

Senthil

Originally Posted by Jonmo1

You're right, it's not a space, it's a hard return, char(160)
But even if it was a space, Trim wouldn't remove it anyway.
Because trim doesn't simply remove spaces....It removes 'extra' spaces.
So a single space between 2 characters is not considered 'extra'
Trim is generally meant for removing leading and trailing spaces.


Anyway, try
=SUBSTITUTE(A1,CHAR(160),"")

Hi Jonmo,

Thanks.. cocenrn given below, please fix this

if i use the said formula i am getting result as "MichaleArun Kumar". The name should reflect as "Micale Arun Kumar" There should be space between Michale and Arun. (Only unknow character should remove and space has to come)

Senthil

Hi Daddylonglegs,

Thanks..

if i use the said formula i am getting result as "MichaleArun Kumar". The name should reflect as "Micale Arun Kumar". space should be there between Michale and Arun. (Only unknow character should remove). Please help me fix this.

Senthil

Well, your post inidicated that you were trying to 'remove' the space or unknown character between Michale and Arun
It didn't say anything about 'replacing' it with a different character.

You can just change
=SUBSTITUTE(A1,CHAR(160),"")
to
=SUBSTITUTE(A1,CHAR(160)," ")

Hi Jonmo,

Thanks it is working fine.

Senthil

You're welcome.