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