Get rid of leading spaces, Trim and Clean doesn't work, Code shows 0

[kangnade]

Hi gurus,

I've read many posts on various forum to solve the problems on the leading space(or space-like stuff), but after trying many functions, including Trim(), Trim(Clean()),Code(Left()), and other ways, I still cannot get rid of it...

Originally I pasted these from a word document, and selected paste with only value, but it doesn't work. Then I re-pasted it into a word, then back into Excel, still doesn't work with Trim() or Trim(Clean()).

I also tried if it is 160, but the Code() shows 0.

Millions of thanks for your help and kindness!

If you need a sample of the problem I am encountering, don't hesitate to leave me an email below I can forward to you. I am new here so I cannot post with attachment...

Thanks a lot!

 

[thisoldman]

Try this,
=SUBSTITUTE(A1,UNICHAR(58853),"")

 

[kangnade]

I know what you are mentioning, but actually I need those foreign characters because this is gonna be a vocab list for foreigners..the thing is that someone else pasted the original source in a word doc, and then I copied the word doc and pasted in excel, then the bizarre thing happened...I actually didn't know what was going on there, but if you check the dropbox file, it seems that the online excel shows this type of symbol: [FONT=宋体]

​But, in the excel file, you don't see it...so I have no idea how to remove that..
[/FONT]

 

[kangnade]

Try this,
=SUBSTITUTE(A1,UNICHAR(58853),"")

Sir, it solves the problem!!!!!!!! Millions of thanks...do you mind telling me how did you figure out the unichar number of this stubborn leading space?

 

[MARK858]

Seeing as thisoldman has worked out what the character was ray: then the below code should work as well..

Sub TestMe()
Range("B2:B16").Replace What:=ChrW(58853), Replacement:="", LookAt:=xlPart
End Sub

 

[thisoldman]

I used another formula:
=UNICODE(LEFT(A1))

These mixed scripts can be confounding. As well as the Chinese characters, the colons in your strings are also wide characters, =UNICHAR(65306)

 

[thisoldman]

I just realized that this would also work for the examples we were given. The formula deletes the first character and any repeats of that character.

=SUBSTITUTE(A1,LEFT(A1),"")

 

[kangnade]

Geez, this is really helpful! Thanks a lot, I really appreciate it!!!