Deleting Special Characters from Excel Text Cell

Hi - Welcome to the board

TRIM and CLEAN are possibly two formula options you may want to look at

A trick I use is to copy the info into Word, either with or without the cell formatting. Word has a find and replace that you can use special characters. I usually have to remove tabs from a file so it will format correctly in notepad. So I find the tabs and replace it with a blank.

Clean & Trim

Option Explicit

Sub Clean_Trim()
'// From Help Files:
'// CLEAN > Removes all nonprintable characters from text.
'// Use CLEAN on text imported from other applications that
'// contains characters that may not print with your
'// operating system.
'// For example, you can use CLEAN to remove some low-level
'// computer code that is frequently at the beginning and end
'// of data files and cannot be printed.

'// TRIM > Removes all spaces from text except for single
'// spaces between words. Use TRIM on text that you have
'// received from another application that may
'// have irregular spacing.

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next

End Sub