Need VBA code to find specific characters and eliminate

[skyport]

What I need to be able to do is have the VBA eliminate any an all letters whenever they are both preceded by a blank space(s) PLUS followed by a period and one or more spaces. However, never delete the letters when any other character is before or after them.

Examples:

( M. )
( P. )

The goal is to eliminate all middle intitials in names and just leave/preserve the first and last names and surrounding data before and after the names

Hoping someone can help.

 

[AlphaFrog]

Try something like this.
Change the column to suit
First test it on a copy of your data.

Columns("A").Replace " ?. ", " ", xlPart

 

[skyport]

Hi Alpha Frog, thanks for helping me. Is there a way to make this run from a module?

 

[AlphaFrog]

[color=darkblue]Sub[/color] Macro1()
    Columns("A").Replace " ?.", "", xlPart
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[skyport]

I get a runtime error 1004 formula too long.

 

[AlphaFrog]

Does this apply to you?

http://www.mrexcel.com/forum/excel-...ime-error-1004-excel-97-formula-too-long.html

 

[skyport]

doesn't say anything about excel 97

 

[skyport]

I did try it on a more recennt excel version and it did run however, it does the following:


Original data before the code

John D. Smith, M.D.

Patricia F. Jones, M.D.

Data after code

John Smith,D.

Patricia Jones,D.

<tbody>
</tbody>

It properly eliminates the middle initial and period but also takes away from the M.D. which is the flaw.

 

[skyport]

Got it to work from your initial idea. Changed the code as follows and it works.


Your original code -

Sub Macro1()
Columns("A").Replace " ?.", "", xlPart
End Sub


Change I made -

Sub Macro1()
Columns("A").Replace " ? .", " ", xlPart
End Sub

 

[skyport]

Correction:

Sub Macro1()
Columns("A").Replace " ?. ", " ", xlPart
End Sub