[[email protected]]

Pls assist with macro how to remove invisible unicode characters in a cell

Thxs
A

[Claus Busch]

Hi,

Am Fri, 11 Mar 2016 20:29:06 -0800 (PST) schrieb :

Pls assist with macro how to remove invisible unicode characters in a cell

try:

Sub ReplaceNPC()
Dim varNPC As Variant
Dim i As Long

With WorksheetFunction
For i = 0 To 31
Range("A1") = Replace(Range("A1"), Chr(i), " ")
Next

varNPC = Array(127, 129, 141, 143, 144, 157)
For i = LBound(varNPC) To UBound(varNPC)
Range("A1") = .Trim(Replace(Range("A1"), Chr(varNPC(i)), " "))
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[GS[_6_]]

Possibly...

[A1] = StrConv([A1], vbFromUnicode)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[ANG]

On Saturday, March 12, 2016 at 3:16:03 PM UTC+4, Claus Busch wrote:
Hi,

Am Fri, 11 Mar 2016 20:29:06 -0800 (PST) schrieb :

Pls assist with macro how to remove invisible unicode characters in a cell

try:

Sub ReplaceNPC()
Dim varNPC As Variant
Dim i As Long

With WorksheetFunction
For i = 0 To 31
Range("A1") = Replace(Range("A1"), Chr(i), " ")
Next

varNPC = Array(127, 129, 141, 143, 144, 157)
For i = LBound(varNPC) To UBound(varNPC)
Range("A1") = .Trim(Replace(Range("A1"), Chr(varNPC(i)), " "))
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

55101210
55101210


Sorry to disturb but its not working on the above two numbers
If you =LEN() YOU GET 9 or 10 which is not correct as you have only 8 visible characters thxs

[ANG]

On Saturday, March 12, 2016 at 6:12:45 PM UTC+4, GS wrote:
Possibly...

[A1] = StrConv([A1], vbFromUnicode)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

AM NEW TO VBA - CAN YOU GIVE ME FULL COMMAND MACRO

[GS[_6_]]

On Saturday, March 12, 2016 at 3:16:03 PM UTC+4, Claus Busch wrote:
Hi,

Am Fri, 11 Mar 2016 20:29:06 -0800 (PST) schrieb
:

Pls assist with macro how to remove invisible unicode characters in
a cell

try:

Sub ReplaceNPC()
Dim varNPC As Variant
Dim i As Long

With WorksheetFunction
For i = 0 To 31
Range("A1") = Replace(Range("A1"), Chr(i), " ")
Next

varNPC = Array(127, 129, 141, 143, 144, 157)
For i = LBound(varNPC) To UBound(varNPC)
Range("A1") = .Trim(Replace(Range("A1"), Chr(varNPC(i)), "
")) Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

55101210
55101210


Sorry to disturb but its not working on the above two numbers
If you =LEN() YOU GET 9 or 10 which is not correct as you have only 8
visible characters thxs

You need to post a link to the file so I have the actual cell contents
because I get 8 as the length.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[GS[_6_]]

I suspect your claim that the cell contains unicode is a wrong
assumption based on the fact that unicode would be 16 characters long
for the sample values you show. Perhaps the cells have non printable
characters instead, as a result of however the data was collected?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion