Remove invisible character

I have a spreadsheet with 7,000 lines and about 30 columns. This data is used to populate a database and is causing problems due to some of the fields containing an odd character. I am attaching a file with only a few examples. The highlighted cells contain the special character.

I have done enough research to learn that the character is seen by Excel as Char 63 or the question mark. It's just not visible. I have tried TRIM, REPLACE, SUBSTITUTE, as well as some specific code samples found throughout EE. No luck though. Any help would be greatly appreciated.


Sample.xls
Dale LoganConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Try running this:

Sub FixData()
    ActiveSheet.UsedRange.Replace ChrW(8233), "", xlPart
End Sub

Open in new window

0
dandrakaCommented:
I found another character as well (8208) in the provided sample.  The code below will reconstruct all your selected cells of codes, eliminating the special character.  Be sure to select the cells you want checked
Sub specCharRemove()
  Dim FormulaCells As Range, Cell As Range
  Dim FormulaSheet As Worksheet
  Dim Row As Integer
  Dim mysh1 As Worksheet
  Dim mysh2 As Worksheet
  Dim Val As String
    
  'On Error Resume Next
  Set mysh1 = Application.ActiveWorkbook.Sheets(1)
  Set FormulaCells = Selection
' Check in selected area
   For Each Cell In FormulaCells
        Val = Cell.Value
        newval = ""
        For i = 1 To Len(Cell)
            If Mid(Val, i, 1) <> ChrW(8208) Then
                If Mid(Val, i, 1) <> ChrW(8233) Then
                    newval = newval & Mid(Val, i, 1)
                End If
            End If
        Next
        Cell.Value = newval
    Next Cell
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale LoganConsultantAuthor Commented:
You guys are amazing. Thanks.
0
dandrakaCommented:
glad to be able to help you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.