• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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
0
Dale Logan
Asked:
Dale Logan
  • 2
1 Solution
 
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
 
Dale LoganConsultantAuthor Commented:
You guys are amazing. Thanks.
0
 
dandrakaCommented:
glad to be able to help you!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now