Removing invisible Hexadecimal characters from the end of a cell 1

[belstoy]

Hello,

I received an excel spreadsheet which contains First Name and Last Name columns. It appears at the end of every name there are invisible hexadecimal characters. How can I remove them, keeping in mind all Names are different lengths?

Belstoy

 

[SkipVought]

Hi,

How many characters? Enter into F1.
[tt]
=(left(A1,len(A1)-$F$1)
[/tt]


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[sawedoff]

Try using =CLEAN(A1). It should remove Non-Printable characters.

Sawedoff

 

[belstoy]

Skip,

The hexadecimal charaters vary from name to name. There is no common amoutn of characters after each name.

Sawedoff,

=clean(a1) did not work. When I press function key F2, it is still showing several invisible characters after each name.

Please advise.

Belstoy

 

[sawedoff]

What kind of "invisible characters" are showing?

Sawedoff

 

[KenWright]

Can you copy the invisible character at all and then paste it into an Edit / Find and replace dialog box, replacing it with nothing.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Hi Belstoy,

Often, data imported from other systems include char 160 instead of char 32 for spaces.

The following 'TrimRange' macro will clean them out, as well as cleaning out any padding via char 32. If you select a range, the macro will run against just the selection; otherwise it processes the whole worksheet. I've included a couple of other routines also for speed & feedback.

Cheers

Option Explicit
Dim SBar As Boolean

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim Percent As Integer
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Replace What:=Chr(160), Replacement:=Chr(32)
Cell.Value = Application.Trim(Cell.Value)
I = I + 1
If Int(I / CellCount * 100 + 0.5) = Percent + 1 Then
Percent = Percent + 1
Application.StatusBar = Percent & "% Trimmed"
End If
Next Cell
MsgBox "Finished trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub
Private Sub MacroEntry()
'Store Status Bar Visibility
SBar = Application.DisplayStatusBar
'Force Status Bar Visibility
Application.DisplayStatusBar = True
' Suspend ScreenUpdating
Application.ScreenUpdating = False
' Stop Re-Calcs
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
' Restore Re-Calcs
Application.Calculation = xlAutomatic
' Remove Message From Status Bar
Application.StatusBar = False
' Restore Status Bar Visibility
Application.DisplayStatusBar = SBar
' Restore ScreenUpdating
Application.ScreenUpdating = True
End Sub

 

[belstoy]

Thanks, MacroPod's code worked great!