Non-Printing Characters: Box with a Question Mark Unable to remove

[New_Here]

Hi Good afternoon to all of you from Sri Lanka,

I am trying to remove the above mentioned square which is around a Question Mark.

Please refer attachment for sample.

My version is office 2016.

Please help Thank you.Unicode issue.xlsx

[mdmackillop]

From here

The problem is that you are working with double byte or unicode text. The CODE function only works with ASCII (single byte) text so it is only picking up part of the two byte character.

The only way I can think of doing this is to use functions that handle unicode text and put a copy of the "white space" double byte character in a special cell so we can use it.

This formula works:

=SUBSTITUTE(A2,O$1,"")

assuming you have a copy of the "white space" character in O1.

[mdmackillop]

Here's a UDF to remove the character

Function MyClean(Data)
    MyClean = Replace(Data, ChrW(&HE04C), "")
End Function

I made up this to determine the character which was copied into another cell.

Sub FindUnicode()
For i = 1 To 128000
If InStr(1, ActiveCell, ChrW("&H" & Hex(i))) Then Exit For
Next
ActiveCell.Offset(, 1) = Hex(i)
End Sub

[New_Here]

Here's a UDF to remove the character

Function MyClean(Data)
    MyClean = Replace(Data, ChrW(&HE04C), "")
End Function

I made up this to determine the character which was copied into another cell.

Sub FindUnicode()
For i = 1 To 128000
If InStr(1, ActiveCell, ChrW("&H" & Hex(i))) Then Exit For
Next
ActiveCell.Offset(, 1) = Hex(i)
End Sub

Thanks a lot for your response, in another forum, a user suggested this equation,

=IF(ISNUMBER(LEFT(C11)+0),C11,REPLACE(C11,1,1,""))

Now this works really well as far as the character I have mentioned is in the left hand side, problem is I sometime have the character in the right hand side end as well.

I have no clue how to use the VB which is mentioned above bro.

How do I run it?

[mdmackillop]

MYClean is used like a normal function. It is added to a code module in the workbook. If you don't use VBA, you'll need to add the Developer tab to the ribbon to access it. You can rename it if you want.

[mdmackillop]

As you have posted this in another forum. please provide links in both forums to the other posts
Please read this
I've renamed your question to make it a more general application.

[New_Here]

MYClean is used like a normal function. It is added to a code module in the workbook. If you don't use VBA, you'll need to add the Developer tab to the ribbon to access it. You can rename it if you want.

Wowwwww ! You are a genius aren't you?

Ok, I checked the attached worksheet and understood what you tried to achieve by the first micro,

What was the second part for? what exactly does it do and how do I use it in the same WORKBOOK?

Because, in the workbook which was attached, I found only the 1st part of your equation was mentioned in the module,

the part you said,

"I made up this to determine the character which was copied into another cell."

you haven't added that.

Why and what exactly does that coding do? Please apologize this Newbie

[mdmackillop]

If you have a similar character, place it on its own in a cell and run FindUnicode. It should show the Hexadecimal for that character in the adjoining cell. That value can be used in the MyClean routine.

[New_Here]

If you have a similar character, place it on its own in a cell and run FindUnicode. It should show the Hexadecimal for that character in the adjoining cell. That value can be used in the MyClean routine.

I came across a small issue, the myclean doesnt seem to work in all cells. Why is that? please refer attachment.

That says a E185 :/

If you could let me know how the 1st vba module needs to be re-adjusted that would be great, so that I will be able to look at other similar codes as well and apply the same.

[mdmackillop]

Add characters to the array as required
Your workbook needs to be saved as an XLSM file to contain the macro coding.

Function MyClean(Data)
    Dim arr, a
    arr = Array(&HE185, &HE04C)
    For Each a In arr
    Data = Replace(Data, ChrW(a), "")
    Next a
    MyClean = Data
End Function

[Paul_Hossler]

If it's ALWAYS in the first position, something like this would work

=RIGHT(C11,LEN(C11)-1)

Otherwise you need something more complicated like Mac's

[New_Here]

If it's ALWAYS in the first position, something like this would work

=RIGHT(C11,LEN(C11)-1)

Otherwise you need something more complicated like Mac's

I finally managed to solve the matter by creating multiple VBA codes i.e MyClear1,2,3 etc and filtered a single cell via colomns, so eventually the cell gets caught to one of the error codes.

Thanks a lot for your support and time.

[New_Here]

Add characters to the array as required
Your workbook needs to be saved as an XLSM file to contain the macro coding.

Function MyClean(Data)
    Dim arr, a
    arr = Array(&HE185, &HE04C)
    For Each a In arr
    Data = Replace(Data, ChrW(a), "")
    Next a
    MyClean = Data
End Function

EXACTLY BROTHER ! EXACTLY .... This is what I needed, but due to time restrains I finally managed to solve the matter by creating multiple VBA codes i.e MyClear1,2,3 etc and filtered a single cell via colomns, so eventually the cell gets caught to one of the error codes.

Thanks a lot for your support and time.

I will update to this code once I have completed my current task.

Thank you again and I apologize if I had troubled you.

[rlv]

Another approach is instead of trying to exclude characters you don't want, is to instead only include the character you do want. Phone numbers lend themselves well to this.

Function CleanContactNumber(ContactNumber, LegalCharacters As String) As String
    Dim Ch As String, CleanNumber As String
    Dim NumStr As String
    Dim I As Long

    NumStr = CStr(ContactNumber)
    CleanNumber = ""
    For I = 1 To Len(NumStr)
        Ch = Mid(NumStr, I, 1)
        If InStr(LegalCharacters, Ch) Then
            CleanNumber = CleanNumber & Ch
        End If
    Next I
    CleanContactNumber = CleanNumber
End Function

So the cell formula would be

=CleanContactNumber(C11,"0123456789-")

Where "0123456789-" are the legal characters.

[rlv]

oops. Minor function error. I was in too much of a hurry. This line was erroneously inside the loop.

CleanContactNumber = CleanNumber

Corrected above and reposting the file here.

[New_Here]

oops. Minor function error. I was in too much of a hurry. This line was erroneously inside the loop.

CleanContactNumber = CleanNumber

Corrected above and reposting the file here.

Wow !!!

You guys seems to be having excel equations for Food.

So many methods to tackle one issue.

Thanks a lot brother for your kind support for this helpless Guy

But I will have to go with mdmackillops method because there are instances where these symbols seem to appear on addresses and many other character fields.

NEVERTHELESS your equation and effort will not go in vain, its saved for my future reference as I am a collector A Big Thank you for all you amazing people. Bye.