VBA Help

[Michael Koerner]

I have received a spread sheet with 1600 rows. The columns are set up as follows
with a couple of data entries.

A B
LastName FirstName
Smith John
Jones (now Brown) Betty

I am going to insert a column between A & B and call it MaidenName What I would
like to know if there is a way through VBA (which I know nothing) to remove the
(now) and leave that name in Col A (LastName) and move the old last name into
the Maiden Name column as below.

A B C
LastName MaidenName FirstName
Smith John
Brown Jones Betty

Would hate to have to do this on a case by case basis, and any help will as
usual be greatly appreciated, as this will be an on going project.

 

[Ken Wright]

No need for code:-

Insert column, assuming data starting A2, in what is now B2 put

=IF(ISERROR(SEARCH("(Now",A2)),"",MID(A2,FIND("(",A2)+5,FIND(")",A2)-FIND("(
",A2)-5))

and copy down as far as needed.

Copy Col B and paste special as values.

Select Col A and do edit / Replace / Replace what = ' (*' (But no quotes -
note leading space though) 'Replace with' just leave blank - Hit OK.

 

[Michael Koerner]

Thanks Ken, works as advertised, but is not quite there. I now need to move all
the information from Col B into Col A if Col A is blank.



--
Regards
Michael Koerner


No need for code:-

Insert column, assuming data starting A2, in what is now B2 put

=IF(ISERROR(SEARCH("(Now",A2)),"",MID(A2,FIND("(",A2)+5,FIND(")",A2)-FIND("(
",A2)-5))

and copy down as far as needed.

Copy Col B and paste special as values.

Select Col A and do edit / Replace / Replace what = ' (*' (But no quotes -
note leading space though) 'Replace with' just leave blank - Hit OK.