What Character Is This?

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

What Character Is This?

Post by jstevens »

I have a good number of cells having a character prefixing some text. I would like to remove it from the cells having tried =CODE(LEFT(G1,1)) which returns 63 but that doesn't appear to be a "diamond" shape in ASCII.
EL20221105.jpg
Your VBA suggestions are appreciated to replace the character with nothing.
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: What Character Is This?

Post by HansV »

Try the following:

=UNICODE(G1)

This should return the decimal Unicode number of the first character in cell G1.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: What Character Is This?

Post by HansV »

You can then use ChrW(...) in the What argument of the Replace method.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: What Character Is This?

Post by jstevens »

Thanks Hans!
Regards,
John

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: What Character Is This?

Post by jstevens »

Hans,

How would one replace two different Unicodes in the What argument of the Replace method? A cell may have one or two different Unicodes. In the screenshot the Unicodes are 61557 and 61611.
EL_Unicode.jpg

I tried using a Select Case statement based on the Unicodes but it would need to circle back if more than one Unicode in a cell.
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: What Character Is This?

Post by HansV »

You can use

Code: Select all

    Range("A1:A10").Replace What:=ChrW(61557), Replacement:="", LookAt:=xlPart
    Range("A1:A10").Replace What:=ChrW(61611), Replacement:="", LookAt:=xlPart
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: What Character Is This?

Post by snb »

Code: Select all

For Each it In [A1:A10]
  it.Value = Replace(Replace(it, ChrW(61557), ""), ChrW(61611), "")
Next

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: What Character Is This?

Post by jstevens »

Hans/snb,

Thanks for your suggestions.
Regards,
John