Code for copying 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

 

[Leah]

Hey Ron,

I just e-mailed out a question in regard to something
along what you are trying to do.
But, I'm having a problem splitting out the last name from
the full name ("Smith" into another cell from "John
Smith" cell.) How did you split it?

Thanks!
Leah

 

[Ron]

If the names are in a consistent format, splitting them
out is no problem. Assuming column A contains the list
of names in format, First-Name Last-Name, with
"John Smith" as an example.

Find the space.
=Find(" ",A1,1)
Returns "5"

FIRST NAME :
In cell B1 use the resulting Find with a Left command
Left(A1,Find(" ",A1,1))
Returns "John"

LAST NAME :
length command can tell you the # characters in a cell
=Len(a1) Returns 10
In cell C1 combine a couple Len's with a Right.
=RIGHT(A1,LEN(A1)-LEN(B1)+1)
(Full Name length minus First Name length +1)
Returns "Smith"

Copy B&C columns and Paste-Special-value to a new columns
and VIOLA, you have the name split!

Bonus step:
The trim command can eliminate spaces
(if you wound up with " Smith")
=Trim(RIGHT(A1,LEN(A1)-LEN(B1)+1))

Between Right, Left, Find, Len, and Trim, you can chop
things up nicely.

Hope this helps.
Ron.