remove anything before period in column

[stat]

Dear all,

I recently imported some data in excel from a .csv file only to find thta
column O (containing last names) has been corrupted somehow. some last
names are mixed up with the last 1 or 2 character of the middle name
e.g S.Simon should really be simon. I would like to get a macro which will
loop through column O and remove anything before a period. In some cases
column may have 2 periods e.g M.G.Taylor when I only want Taylor. The macro
should remove everything before Taylor

Please assist

Many thanks

 

[William]

Hi

To do it without a macro.....
Select Column O
From Excel toolbar, "Edit>Replace"
In the "Find What" box, enter (without inverted commas) "*."
Leave the "Replace With" box empty.

To do it with a macro.....
Sub Test()
Columns("O:O").Replace What:="*.", Replacement:="", LookAt:=xlPart
End Sub

--
XL2002
Regards

William
(e-mail address removed)

| Dear all,
|
| I recently imported some data in excel from a .csv file only to find thta
| column O (containing last names) has been corrupted somehow. some last
| names are mixed up with the last 1 or 2 character of the middle name
| e.g S.Simon should really be simon. I would like to get a macro which
will
| loop through column O and remove anything before a period. In some cases
| column may have 2 periods e.g M.G.Taylor when I only want Taylor. The
macro
| should remove everything before Taylor
|
| Please assist
|
| Many thanks
|
|
|