Forum

Macro to clear Unwa...
 
Notifications
Clear all

Macro to clear Unwanted data from PDF Conversion

5 Posts
2 Users
0 Reactions
87 Views
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

Macro to clear Characters (excelforum.com)

Macro to clear Characters | MrExcel Message Board

 
Posted : 09/06/2021 3:20 am
(@purfleet)
Posts: 412
Reputable Member
 

what characters are you struggling with?

 
Posted : 10/06/2021 12:46 pm
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

I am struggling to clear the characters below

 



,___�

 

I have attached a workbook containing these characters

 

It would be appreciated if you could kindly amend my code so as to clear these

 
Posted : 11/06/2021 12:13 am
(@purfleet)
Posts: 412
Reputable Member
 

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

 
Posted : 11/06/2021 1:51 pm
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

Thanks for the help

 
Posted : 11/06/2021 2:02 pm
Share: