Remove punctuation from Excel text 1

[johnbarr44]

I would like to remove punctuation from a selection of columns containing text (in Excel 2003).

In particular, the following four characters , . ' "

Is it possible to write a macro to achieve that?

Thanks
John B

 

[SkipVought]

hi,

Turn on your Macro recorder and record doing a Replace.

Post back with your recorded code to get help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Hi Skip,

I think this is it ??

Sub Punctuation()
'
' Punctuation Macro
' Macro recorded 2/08/2012 by John B
'
' Keyboard Shortcut: Ctrl+p
'
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

John B

 

[SkipVought]

Good! Then ctr+p should work.

Does it?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Thanks for pointing me in the right direction, Skip.

I finally figured it was necessary to run the "Replace" function four times in the same macro to eliminate all four punctuation items.

John B

 

[SkipVought]

A one shot solution...

Sub Punctuation()
'
' Punctuation Macro
' Macro recorded 2/08/2012 by John B
'
' Keyboard Shortcut: Ctrl+p
'
    Dim CHRS(3) As String, i As Integer
    
    CHRS(0) = ","
    CHRS(1) = "."
    CHRS(2) = "'"
    CHRS(3) = Chr(34)

    For i = 0 To UBound(CHRS)
        Cells.Replace What:=CHRS(i), Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Thank you, Skip.
That's certainly a lot tidier than my effort.

Is there a way to include an instruction in the macro so that it runs only on a selection, i.e. so I can select any group of cells before running the macro, rather than have it work on the whole sheet. I have searched for an answer to this seemingly simple request, but have not been able to find the answer.

John B

 

[SkipVought]

replace Cells with Selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Ahh! So simple, but frustratingly hard to find for a beginner.
(old fuddy duddy!)

Thanks again, Skip.

John B

 

[SkipVought]

I understand. No problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}