I'm trying to figure out how to combine a number of columns of data to
format First & Last names for a mailing list. The problem is each
record/row can contain different infomation depending on the source. Here's
an illustration:

Col A Col B ColC ColD ColE
Bob & Mary A Jones
Bill
Williams
Pete R & Sue Smith
etc

When done I would like:

Col A Col B ColC ColD ColE
Bob & Mary Jones
Bill
Williams
Pete & Sue
Smith

Here's a linl to a jpeg of the colunms in my Excell file...is there any way
to write a formula that would accomplish the above...even a set of forulas
to run would be great...ususally have about 3000 records to due and going
line by line is nuts !?

http://scottsdalearizona-homesforsal...ages/excel.jpg

Thanks, Tim

Talk about put putting round pegs into square holes, and your
representation is rearranged, plus you have banknames that
have to be read from left to right. I'd include the single letters
the lastname is in the first of the columns of interest (column D) [untested]
=IF(FIND(" BANK ", UPPER(" " & D2 & " " & E2 & " " & F2 & " " & G2 " " & H2 & " " & C2 & " "),
TRIM(C2 & " " & D2 & " " & E2 & " " & F2 " " & G2 & " " & H2),
TRIM(D2 & " " & E2 & " " & F2 & " " & G2 " " & H2 & " " & C2))

Does not seem worth the effort to remove single letter initials
which could be very important and only cost you four character
spaces at most, if present. You have two more columns that
are being dropped. And family holdings or whatever YFF stands
for get truncated.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tim" wrote in message ...
I'm trying to figure out how to combine a number of columns of data to
format First & Last names for a mailing list. The problem is each
record/row can contain different infomation depending on the source. Here's
an illustration:

Col A Col B ColC ColD ColE
Bob & Mary A Jones
Bill
Williams
Pete R & Sue Smith
etc

When done I would like:

Col A Col B ColC ColD ColE
Bob & Mary Jones
Bill
Williams
Pete & Sue
Smith

Here's a linl to a jpeg of the colunms in my Excell file...is there any way
to write a formula that would accomplish the above...even a set of forulas
to run would be great...ususally have about 3000 records to due and going
line by line is nuts !?

http://scottsdalearizona-homesforsal...ages/excel.jpg

Thanks, Tim

Corrected formula: for use somewhere on Row 2
=IF(ISERR(SEARCH(" BANK ", UPPER(" " & D2 & " " & E2 & " " & F2 & " " & G2 & " " & H2 & " " & C2 & " "))),TRIM(D2 & " " & E2 & " " &
F2 & " " & G2 & " " & H2 & " " & C2),TRIM(C2 & " " & D2 & " " & E2 & " " & F2 & " " & G2 & " " & H2))


"David McRitchie" wrote in message ...
Talk about put putting round pegs into square holes, and your
representation is rearranged, plus you have banknames that
have to be read from left to right. I'd include the single letters
the lastname is in the first of the columns of interest (column D)
.... see corrected formula in this reply ...
Does not seem worth the effort to remove single letter initials
which could be very important and only cost you four character
spaces at most, if present. You have two more columns that
are being dropped. And family holdings or whatever YFF stands
for get truncated.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tim" wrote in message ...
I'm trying to figure out how to combine a number of columns of data to format First & Last names for a mailing list. The problem
is each record/row can contain different infomation depending on the source. Here's an illustration:

Col A Col B ColC ColD ColE
Bob & Mary A Jones
Bill Williams
Pete R & Sue Smith
etc

When done I would like:

Col A Col B ColC ColD ColE
Bob & Mary Jones
Bill Williams
Pete & Sue Smith

Here's a linl to a jpeg of the colunms in my Excell file...is there any way to write a formula that would accomplish the
above...even a set of forulas to run would be great...ususally have about 3000 records to due and going line by line is nuts !?

http://scottsdalearizona-homesforsal...ages/excel.jpg

Thanks, Tim

Thanks David...I knew this would raise a eyebrow or two...These columns are
created by the "text to column" function...the various columns in my excel
jpeg are actually created from a single cell input and then expanded by text
to column to the resulting jpeg sample using the space delimiter in text to
column.

The bank names and a few others are not important since these are all single
family residences...and I replace anything but peoples names with 'resident'
or 'current owner' in the mailings

Would it be easier or more prudent to work with the original cell with all
the info than to break it into columns as I have...?...here's a sample
cell...but remember...the cell contents can very widely..from one initial
and last name to a seven word entry including Trust, Inc, Co...and so on ?

original format comes two ways:

DAMACENO LUZIVONE & ANTONIA M

or
LUZIVONE & ANTONIA M DAMACENO



Better to run the text to column function...or some other function on the
original cell

Thanks, Tim


"David McRitchie" wrote in message
...

Corrected formula: for use somewhere on Row 2
=IF(ISERR(SEARCH(" BANK ", UPPER(" " & D2 & " " & E2 & " " & F2 & " " & G2
& " " & H2 & " " & C2 & " "))),TRIM(D2 & " " & E2 & " " & F2 & " " & G2 &
" " & H2 & " " & C2),TRIM(C2 & " " & D2 & " " & E2 & " " & F2 & " " & G2
& " " & H2))


"David McRitchie" wrote in message
...
Talk about put putting round pegs into square holes, and your
representation is rearranged, plus you have banknames that
have to be read from left to right. I'd include the single letters
the lastname is in the first of the columns of interest (column D)
.... see corrected formula in this reply ...
Does not seem worth the effort to remove single letter initials
which could be very important and only cost you four character
spaces at most, if present. You have two more columns that
are being dropped. And family holdings or whatever YFF stands
for get truncated.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tim" wrote in message
...
I'm trying to figure out how to combine a number of columns of data to
format First & Last names for a mailing list. The problem is each
record/row can contain different infomation depending on the source.
Here's an illustration:

Col A Col B ColC ColD ColE
Bob & Mary A Jones
Bill Williams
Pete R & Sue Smith
etc

When done I would like:

Col A Col B ColC ColD ColE
Bob & Mary
Jones
Bill Williams
Pete & Sue Smith

Here's a linl to a jpeg of the colunms in my Excell file...is there any
way to write a formula that would accomplish the above...even a set of
forulas to run would be great...ususally have about 3000 records to due
and going line by line is nuts !?

http://scottsdalearizona-homesforsal...ages/excel.jpg

Thanks, Tim

The second form is what I was trying to achieve (leaving initials intact)
What you might want to do is to use a proportional font, and
convert the text to proper text. See
http://www.mvps.org/dmcritchie/excel/proper.htm
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tim" wrote in message ...
Thanks David...I knew this would raise a eyebrow or two...These columns are
created by the "text to column" function...the various columns in my excel
jpeg are actually created from a single cell input and then expanded by text
to column to the resulting jpeg sample using the space delimiter in text to
column.

The bank names and a few others are not important since these are all single
family residences...and I replace anything but peoples names with 'resident'
or 'current owner' in the mailings

Would it be easier or more prudent to work with the original cell with all
the info than to break it into columns as I have...?...here's a sample
cell...but remember...the cell contents can very widely..from one initial
and last name to a seven word entry including Trust, Inc, Co...and so on ?

original format comes two ways:

DAMACENO LUZIVONE & ANTONIA M

or
LUZIVONE & ANTONIA M DAMACENO



Better to run the text to column function...or some other function on the
original cell

Thanks, Tim


"David McRitchie" wrote in message
...

Corrected formula: for use somewhere on Row 2
=IF(ISERR(SEARCH(" BANK ", UPPER(" " & D2 & " " & E2 & " " & F2 & " " & G2
& " " & H2 & " " & C2 & " "))),TRIM(D2 & " " & E2 & " " & F2 & " " & G2 &
" " & H2 & " " & C2),TRIM(C2 & " " & D2 & " " & E2 & " " & F2 & " " & G2
& " " & H2))


"David McRitchie" wrote in message
...
Talk about put putting round pegs into square holes, and your
representation is rearranged, plus you have banknames that
have to be read from left to right. I'd include the single letters
the lastname is in the first of the columns of interest (column D)
.... see corrected formula in this reply ...
Does not seem worth the effort to remove single letter initials
which could be very important and only cost you four character
spaces at most, if present. You have two more columns that
are being dropped. And family holdings or whatever YFF stands
for get truncated.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tim" wrote in message
...
I'm trying to figure out how to combine a number of columns of data to
format First & Last names for a mailing list. The problem is each
record/row can contain different infomation depending on the source.
Here's an illustration:

Col A Col B ColC ColD ColE
Bob & Mary A Jones
Bill Williams
Pete R & Sue Smith
etc

When done I would like:

Col A Col B ColC ColD ColE
Bob & Mary
Jones
Bill Williams
Pete & Sue Smith

Here's a linl to a jpeg of the colunms in my Excell file...is there any
way to write a formula that would accomplish the above...even a set of
forulas to run would be great...ususally have about 3000 records to due
and going line by line is nuts !?

http://scottsdalearizona-homesforsal...ages/excel.jpg

Thanks, Tim