Function to copy lines

[Ron]

I'm in the process of converting a monster Excel sheet.

There are thousands of addresses with:
Mr. and Mrs. First/Last Name

Part of the process is breaking these into separate
fields (salutation, first name, last name, etc). I can
take care of that without a problem, but what I am having
problems with is splitting this one line into two.

I need to duplicate the rows in cases where the cell
starts with "Mr. and Mrs." For example
Line1 - Mr. and Mrs. John Smith
would get coverted to
Line1 - Mr. and Mrs. John Smith
Line2 - Mr. and Mrs. John Smith

This might seem strange, but the end result is going to
be
Line1 - Mr. John Smith
Line2 - Mrs. Jane Smith
A user will have to look up the spouse name from a
different location, but I am trying to keep them from
inserting new lines. All they have to do is edit the
lines that already exits.

Anyone have an idea how to go about this?

Thanks for the info!
RonJ

 

[Jonathan Rynd]

I need to duplicate the rows in cases where the cell
starts with "Mr. and Mrs." For example
Line1 - Mr. and Mrs. John Smith
would get coverted to
Line1 - Mr. and Mrs. John Smith
Line2 - Mr. and Mrs. John Smith

The easy way to do this is to write a VB script. VB has the Insert command
that allows you to easily insert a row between two other rows.

While it is possible to do using FIND, SUM, and OFFSET/INDEX, I would
strongly discourage you from trying to use worksheet functions to
accomplish this task. Stick with VB.