December 8, 2016
i have a PDF documents which I have converted into Excel
Most of the data is converted correctly except a few unwanted characters
I have written code to clear most of the unwanted characters, but there are a few characters that I am battling to write code to clear using VBA
It would be appreciated if someone could kindly assist me
Sub ClearUnwantedCharacterst() Dim A As String * 1 Dim B As String * 1 Dim i As Integer Dim S As String Const AccChars = "ñéúãíçóêôöáCO Z 1-o oCJ Z5Di?t;<u[5 5)Q.U»D)? =>p?Lz«+ kM?Nq::?Print" 'using less characters is faster" Const RegChars = "neuaicoeooa" Range("A1").Resize(Cells.Find(what:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _ Cells.Find(what:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select ' For Each cell In Selection If cell <> "" Then S = cell.Text For i = 1 To Len(AccChars) A = Mid(AccChars, i, 1) B = Mid(RegChars, i, 1) S = Replace(S, A, B) Next cell.Value = S Debug.Print "celltext "; (cell.Text) End If Next cell End Sub
i posted a few days ago on two Websites, but have had no replies
Trusted Members
December 20, 2019
Trusted Members
December 20, 2019
The black dimond question mark is unichar 65533 <=UNICODE(a8)>, once you have that you can run something like
Sub CLEARRUBBISH()
Cells.Replace WHAT:=WorksheetFunction.Unichar(65533), REPLACEMENT:=""
End Sub
You might need multiple runs if a cell contains more than one rubbish character
1 Guest(s)