List with commas between; multiple IF statements.

[sharpmel]

I am describing a person's racial background based on survey output. One can select multiple races including an Other option with text. I am having a problem with the commas in between races.

For example, you can see that there are extra commas if not all are selected. "John Jingle is a 10 month old , , , Asian, Native Hawaiian or Other Pacific Islander boy."

This is what I have (between the **s is the list that needs the commas).

='Qualtrics Output'!R3
& " "
& 'Qualtrics Output'!S3
& " is a "
& IF(DATE(YEAR('Qualtrics Output'!N3)+2,MONTH('Qualtrics Output'!N3),DAY('Qualtrics Output'!N3))-'Qualtrics Output'!Q3>0,DATEDIF('Qualtrics Output'!N3,'Qualtrics Output'!Q3,"m")& " month old ",DATEDIF('Qualtrics Output'!N3,'Qualtrics Output'!Q3,"y") & " year old ")
& IF(ISBLANK('Qualtrics Output'!AA3), "", LOOKUP('Qualtrics Output'!AA3,{0,1},{"non-Hispanic ","Hispanic "}))
**************************
& IF('Qualtrics Output'!AB3=1,"American Indian or Alaskan Native","")
& IF(OR('Qualtrics Output'!AC3=1, 'Qualtrics Output'!AD3=1, 'Qualtrics Output'!AE3=1, 'Qualtrics Output'!AF3=1, 'Qualtrics Output'!AH3=1), ", ","")
& IF('Qualtrics Output'!AC3=1,"African American", "")
&IF(OR('Qualtrics Output'!AD3=1, 'Qualtrics Output'!AE3=1, 'Qualtrics Output'!AF3=1, 'Qualtrics Output'!AH3=1), ", ","")
& IF('Qualtrics Output'!AD3=1,"Caucasian","")
& IF(OR('Qualtrics Output'!AE3=1, 'Qualtrics Output'!AF3=1, 'Qualtrics Output'!AH3=1), ", ","")
& IF('Qualtrics Output'!AE3=1,"Asian","")
& IF(OR('Qualtrics Output'!AF3=1, 'Qualtrics Output'!AH3=1), ", ","")
& IF('Qualtrics Output'!AF3=1,"Native Hawaiian or Other Pacific Islander","")
& IF(ISTEXT('Qualtrics Output'!AH3), ", ","")
& IF('Qualtrics Output'!AH3="","",'Qualtrics Output'!AH3)
**************************
& LOOKUP('Qualtrics Output'!Z3,{1,2,3},{" girl."," boy."," child whose gender is yet to be determined."})

untitled.jpg
My reference table ^^

[JBeaucaire]

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates the problem and your desired results. Use BEFORE/AFTER sheets if that helps make it clearer.

[sharpmel]

PAT Automated Scoring and Report_Draft 12 10 12.xlsx
Formula is on sheet Text Report.

[JBeaucaire]

One way to check/fix things like this... try this in C14 to eliminate some of them:

=SUBSTITUTE(A14, ", , ", ", ")

You can nest several of those together to get most of them:

=SUBSTITUTE(SUBSTITUTE(A14, ", , ", ", "), ", , ", ", ")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14, ", , ", ", "), ", , ", ", "), ", , ", ", ")


With that idea you can fix the long strings in column B.

[sharpmel]

Don't I have to modify that formula every time I get different results then? (If I have 5 listed and 4 commas vs. 4 listed and 3 commas)

[JBeaucaire]

No, I showed you how to nest your formula inside 3 SUBSTITUTE() loops which should remove all of them. You tested this?

[sharpmel]

I tested it in C14 and it seems to work. There's no way to modify it all within cell A14 though, right?

[JBeaucaire]

Yes, that was an example. That was to show you the technique.

Now, you use that formula IN cell A14, and replace the reference to A14 in the middle of the formula with the current existing formula.

[sharpmel]

Thank you. I guess I'm just a bit slow.