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

 

[kevin9999]

One way with Replace (both options run from the active sheet)
1. for a used range

Sub Lose_Thorn()
    With ActiveSheet.UsedRange
        .Replace "item?þ", "item", xlPart
        .Replace "itemþ?", "item", xlPart
    End With
End Sub

2. for a table (change "Table1" to actual table name)

Sub Lose_Thorn_Table()
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        .Replace "item?þ", "item", xlPart
        .Replace "itemþ?", "item", xlPart
    End With
End Sub

 

[Ali M]

thanks
but I just give you example after ITEM word but there are many different words QTY?þ, BRAND?þ ,...
so I want dealing with any cell contains this symbol in table or range .

 

[kevin9999]

Assuming you don't want to keep any of the question marks...

Sub Lose_Thorn()
    With ActiveSheet.UsedRange
        .Replace "þ", "", xlPart
        .Replace "~?", "", xlPart
    End With
End Sub

Sub Lose_Thorn_Table()
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        .Replace "þ", "", xlPart
        .Replace "~?", "", xlPart
    End With
End Sub

 

[Ali M]

unfortunately doesn't clean anything !

 

[kevin9999]

unfortunately doesn't clean anything !

Are you running when the sheet with the symbols on it is active?

Before used range version:

thorn.xlsm
	A	B	C	D	E	F	G	H
1	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
2	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
3	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
4	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
5	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
6	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
7	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
8	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
9	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
10	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
Sheet1

After:

thorn.xlsm
	A	B	C	D	E	F	G	H
1	item	data	item	data	item	data	item	data
2	x	item	x	item	x	item	x	item
3	item	data	item	data	item	data	item	data
4	x	item	x	item	x	item	x	item
5	item	data	item	data	item	data	item	data
6	x	item	x	item	x	item	x	item
7	item	data	item	data	item	data	item	data
8	x	item	x	item	x	item	x	item
9	item	data	item	data	item	data	item	data
10	x	item	x	item	x	item	x	item
Sheet1

Before Table version:

thorn.xlsm
	A	B	C	D	E	F	G	H
1	HDR1	HDR2	HDR3	HDR4	HDR5	HDR6	HDR7	HDR8
2	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
3	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
4	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
5	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
6	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
7	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
8	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
9	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
10	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ	item?þ	data?þ
11	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?	xþ?	itemþ?
Sheet3

After:

thorn.xlsm
	A	B	C	D	E	F	G	H
1	HDR1	HDR2	HDR3	HDR4	HDR5	HDR6	HDR7	HDR8
2	item	data	item	data	item	data	item	data
3	x	item	x	item	x	item	x	item
4	item	data	item	data	item	data	item	data
5	x	item	x	item	x	item	x	item
6	item	data	item	data	item	data	item	data
7	x	item	x	item	x	item	x	item
8	item	data	item	data	item	data	item	data
9	x	item	x	item	x	item	x	item
10	item	data	item	data	item	data	item	data
11	x	item	x	item	x	item	x	item
Sheet3

If you can't get it to work, then I'll need to see your actual data using the XL2BB add in before I'll look at it any further.

 

[Ali M]

the problem is symbols nobody can't see them even enter inside the cells .
one way to find out just copy cell containing and paste in vba editor then will show theses strange symbols.

 

[kevin9999]

the problem is symbols nobody can't see them even enter inside the cells .
one way to find out just copy cell containing and paste in vba editor then will show theses strange symbols.

I'm afraid I don't understand any of that.

 

[Ali M]

I'm afraid I don't understand any of that.

based on your picture the symbols are showed , but based on my data are not . you can say just showing item , data without see any symbols inside the table.
accidently I've found these symbols when I try renaming headers and I use copy from inside cell to inside the code then show symbols without show any symbols when you cee the cells inside the table (so there are non-printable characters or something in there)
in other meaning :
On the surface of the data, there is no symbol, but in reality there are incomprehensible symbols inside the cells.

 

[kevin9999]

based on your picture the symbols are showed , but based on my data are not . you can say just showing item , data without see any symbols inside the table.
accidently I've found these symbols when I try renaming headers and I use copy from inside cell to inside the code then show symbols without show any symbols when you cee the cells inside the table (so there are non-printable characters or something in there)
in other meaning :
On the surface of the data, there is no symbol, but in reality there are incomprehensible symbols inside the cells.

I feel we're having a communication problem now. I don't believe that I will be able to make any further progress on this, and I'm sorry I wasn't able to help you with a solution to your problem. Hopefully someone else will be able to step in and assist you further. Good luck.