VBA Help
 

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.

--

Regards
Michael Koerner

VBA Help
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Michael Koerner" wrote in message
...
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.

--

Regards
Michael Koerner

VBA Help
 

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


"Ken Wright" wrote in message
...
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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Michael Koerner" wrote in message
...
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.

--

Regards
Michael Koerner