How to remove all special characters and punctuation from excel sheet

[jaimond]

Remove all special characters and punctuation from excel sheet

 

[Peter_SSs]

Re: How to remove all special cherecters and pauntuations from excel sheet

Remove all special characters and punctuation from excel sheet

Could you provide a comprehensive list so that we know exactly what you want removed?

How big is your data and where is it? That is, what columns/rows need to be checked?

Could you provide a few bits of sample data and the expected results for that data?

 

[ParamRay]

Re: How to remove all special cherecters and pauntuations from excel sheet

Agree with Peter - it depends on what you mean by "special" characters, and also where you are searching for these characters. For example, are you only looking to remove them from cells, or are they also contained within objects in the drawing layer, such as shapes and textboxes.

This code sample would remove all non-alphanumeric characters from cell values (constants) in a worksheet:

Sub DeleteNonAlphaNumericChars()
  Dim Constants As Range
  Dim Char As String
  Dim Txt As String
  Dim Cell As Range
  Dim i As Integer
  
' Change sheet name as needed:
  Const SheetName = "Sheet1"
  
  Set Constants = ThisWorkbook.Sheets( _
                  SheetName).Cells.SpecialCells( _
                  xlCellTypeConstants)
  
  For Each Cell In Constants
    Txt = vbNullString
    For i = 1 To Len(Cell.Text)
      Char = Mid(Cell.Text, i, 1)
      Select Case Asc(Char)
        Case Asc("0") To Asc("9"):
          Txt = Txt & Char
        Case Asc("a") To Asc("z"):
          Txt = Txt & Char
        Case Asc("A") To Asc("Z"):
          Txt = Txt & Char
      End Select
    Next i
    Cell.Value = Txt
  Next Cell
End Sub

 

[jaimond]

Re: How to remove all special cherecters and pauntuations from excel sheet

Yes ParamRay this is what i am looking for. I want to retain only number and letters.

 

[Peter_SSs]

Re: How to remove all special cherecters and pauntuations from excel sheet

I want to retain only number and letters.

So if the text is

Simon's book is red.

you want it changed to

Simons book is red

That is, remove both the period at the end and the apostrophe?


.. and what about this question?

How big is your data and where is it? That is, what columns/rows need to be checked?

 

[Rick Rothstein]

Re: How to remove all special cherecters and pauntuations from excel sheet

So if the text is

Simon's book is red.

you want it changed to

Simons book is red

That is, remove both the period at the end and the apostrophe?

To follow up on Peter's questions, what about hyphenated words. For example...

The report is due mid-October.

The totals are up-to-date as of yesterday.

Did you want the hyphens removed as well?