How to combine columns 'conditionally' ?

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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-homesforsale.com/images/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.
 
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 said:
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)
Click to expand...
.... see corrected formula in this reply ...
 
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
 
Back
Top