how clean strange symbols(?þ) from Table or range

[Ali M]

Hi experts
I face dilemma when I received file by another person .
it shows after name like this :

item?þ
itemþ?
so how can I delete these from all of my table or in used range to become like this
item
is there any macro to clean the cells in table or in used range?
this symbols cause me big troubles with another macros(can't run)
thanks

 

[Ali M]

yes that's correct , but I forgot saying this symbol # could be just in column F , as to others columns contains Arabic characters .

 

[MARK858]

I'll have another look at this later this evening

 

[Ali M]

I hope to find a solution on your hand because it seems none of the members will go into a topic like this though 118 views for this thread .

 

[MARK858]

Can you try running the code below.
IMPORTANT: Copy and paste it into your code window, do not type/retype any part of it

Sub Remove8207()
    With ActiveSheet.UsedRange
        .Replace What:="~?", Replacement:="", LookAt:=xlPart
        .Replace What:=ChrW(254), Replacement:="", LookAt:=xlPart
    End With
End Sub

 

[MARK858]

Also try the below. It is still designed to act only on column A as we are testing

Sub UniKiller2()
    Dim s As String, temp As String, i As Long, x As Long
    Dim C As String

    ActiveSheet.UsedRange.Replace What:=ChrW(254), Replacement:="", LookAt:=xlPart
   
  For x = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    s = Cells(x, 1).Value
  
    temp = ""

    For i = 1 To Len(s)
        C = Mid(s, i, 1)
        If AscW(C) < 8207 Or AscW(C) > 8207 Then
            temp = temp & C
        End If
    Next i
    Cells(x, 1).Value = temp
  Next
End Sub

 

[Ali M]

thanks for two codesز
first doesn't change any thing
second before running
this what showing as in picture but I can't copy and paste here because will change symbol.

after run the macro will delete one symbol not all to become like this
الرصيد?

 

[MARK858]

I'm not in yet but can you check if the formulas I gave you earlier are still returning the same numbers now one of the characters is gone please.

 

[Ali M]

here is the values again

ssss.xlsm
	H	I
1	8236	63
2	56	56
3	50	50
4	51	51
5	48	48
6	55	55
7	55	55
8	48	48
9	52	52
10	48	48
11	54	54
12	55	55
13	57	57
14	53	53
15	48	48
16	49	49
17	48	48
18	50	50
19	48	48
20	56	56
21	56	56
22	54	54
23	48	48
24	54	54
25	54	54
26	48	48
27	53	53
28	48	48
29	48	48
30	54	54
31	48	48
32	48	48
33	52	52
34	55	55
35	51	51
36	48	48
37	48	48
38	57	57
39	48	48
40	51	51
41	48	48
42	56	56
43	49	49
44	54	54
45	56	56
46	55	55
47	53	53
48	54	54
49	50	50
50	48	48
51	53	53
52	48	48
53	54	54
54	53	53
55	48	48
56	48	48
57	48	48
58	48	48
59	48	48
60	56	56
61	50	50
62	54	54
63	53	53
64	55	55
65	48	48
66	56	56
67	57	57
68	52	52
69	48	48
70	48	48
71	48	48
72	53	53
73	48	48
74	53	53
75	48	48
76	54	54
77	56	56
78	48	48
79	48	48
80	49	49
81	53	53
82	56	56
83	48	48
84	56	56
85	56	56
86	52	52
Sheet1

Cell Formulas
Range	Formula
H1:H86	H1	=UNICODE(RIGHT(A1,1))
I1:I86	I1	=CODE(RIGHT(A1,1))

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Sheet1!$A$1:$F$86	H1:I1

 

[MARK858]

Try changing

ActiveSheet.UsedRange.Replace What:=ChrW(254), Replacement:="", LookAt:=xlPart

to

ActiveSheet.UsedRange.Replace What:=Chr(63), Replacement:="", LookAt:=xlPart

 

[Ali M]

unfortunately will clear all of data and rename headers in row1( " column1", "column2......)