Make repetitive IF formula more concise?

Hello

I use this formula in many of my spreadsheets for last/first/middle name:


=CHAR(10)&
UPPER(IF(C4="","",C4&", "&C5&IF(C6="",""," "&C6)&REPT(CHAR(10),2))
&IF(D4="","",D4&", "&D5&IF(D6="",""," "&D6)&REPT(CHAR(10),2))
&IF(E4="","",E4&", "&E5&IF(E6="",""," "&E6)&REPT(CHAR(10),2))
&IF(F4="","",F4&", "&F5&IF(F6="",""," "&F6)&REPT(CHAR(10),2))
&IF(G4="","",G4&", "&G5&IF(G6="",""," "&G6)&REPT(CHAR(10),2))
&IF(H4="","",H4&", "&H5&IF(H6="",""," "&H6)&REPT(CHAR(10),2))
&IF(I4="","",I4&", "&I5&IF(I6="",""," "&I6)&REPT(CHAR(10),2))
&IF(J4="","",J4&", "&J5&IF(J6="",""," "&J6)&REPT(CHAR(10),2))
&IF(K4="","",K4&", "&K5&IF(K6="",""," "&K6)&REPT(CHAR(10),2)))


Is there a more concise formula for this since it is so repetitive?

Thanks for any help!

VR/Lost

Hi leaning

I, for one, am finding it difficult to understand what your formula is meant to do.

You would do best to post a sample workbook showing your Sheet Layout and Before and After examples.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

Marcol,

Cleaning a worksheet will take some time, but maybe I can explain better.

Say you had a group of people (nine people max) who you were going to send a letter to.

So depending on what names where on those cells determines who is on the "To:
block of the letter.

So the formula for the actual "To:" block would be:

="To: "& UPPER(IF(C4="","",C4&", "&C5&IF(C6="",""," "&C6)&REPT(CHAR(10),2))
&IF(D4="","",D4&", "&D5&IF(D6="",""," "&D6)&REPT(CHAR(10),2))
&IF(E4="","",E4&", "&E5&IF(E6="",""," "&E6)&REPT(CHAR(10),2))
....

The output would be:

To: SMITH, JOHN RICHARD

JONES, MICHAEL WILLIAM

JACKSON, SAMUEL L.

Since only three names were entered, that ends the formula. More names = a longer list.

I was wondering if there was a trick to squash the formula down since it looks at C4,C5,C6, then E4,E5,E6, then F4,F5,F6, etc.

HTH!

VR/Lost

Marcol,

Yes.

Person 1 LName is in C4, FName in C5, MName plus Suffix (if any) in C6.
Person 2 LName in D4, FName in D5, MName in D6
Person 3 LName in E4, FName in E5, MName in E6, etc.
.
.
.
Person 9 LName in K4, FName in K5, MName in K6.

The concatenating/IF formula is in L15.

You would have an empty block if the person didn't have a middle name, or maybe didn't have a last name (Cher, Madonna, etc.).

The formula I am using works; I just am curious whether there is a range or looping or something formula that would work that isn't so wordy.

HTH!

VR/Lost

Marcol,

I think a UDF would be more complicated and more work than a long concatenate equation.

This'll work.

I appreciate your help in looking at this and seeing if there was a better way.

Thanks!

VR/Lost