extra characters returned when no needed

OshaK

Thank you to this wonderful community for helping me in so many ways!

I have another formula-related question:

I have several columns with drop-down menus for different demographic groups, and I need to create a combined column with just the names (only one name per row. the demographic groups see only their column).

I've used this formula from another example this group helped me solved:

=IF([OHNS Faculty]@row <> " ", [OHNS Faculty]@row) + IF([OHNS Affiliated Faculty]@row <> " ", [OHNS Affiliated Faculty]@row) + IF([OHNS CIs/Fellows/Instructor]@row <> " ", [OHNS CIs/Fellows/Instructor]@row) + IF([OHNS Resident]@row <> " ", [OHNS Resident]@row) + IF([OHNS Administrative Staff]@row <> " ", [OHNS Administrative Staff]@row) + IF([Other Full Name]@row <> " ", [Other Full Name]@row)

but i get a "0" if it's any column but "OHNS Faculty" and extra commas for all the columns, but the last one "Other Full Name" like so:

• OHNS Faculty Last Name, OHNS Faculty First Name,
• 0OHNS CIs/Fellows/Instructor Last Name, OHNS CIs/Fellows/Instructor First Name,
• 0OHNS Resident Last Name, OHNS Resident First Name,
• 0OHNS Administrative Staff Last Name, OHNS Administrative Staff Last Name,
• 0Other Full Name Last Name, Other Full Name Last Name

How can i get rid of zero at the beginning and a comma at the end of each name (in between Last/First name is fine).

Thank you!

Carson Penticuff

Try this one instead. I wasn't sure if you would ever have only the first name or last fields populated, so this accounts for that possibility as well. If they are both blank, the cell will be blank. That should eliminate the extra comma issue.

=IF(AND([Last Name]@row <> "", [First Name]@row <> ""), [Last Name]@row + "," + [First Name]@row, IF([Last Name]@row <> "", [Last Name]@row, IF([First Name]@row <> "", [First Name]@row, "")))

Carson Penticuff

Are the six column references consecutive in your sheet? If so, you can do this with a much shorter formula.

=JOIN([OHNS Faculty]@row:[Other Full Name]@row)

OshaK

@Carson Penticuff thank you, yes, that works. Though it still returns a comma from the [Other Full Name] column (that column combines the columns Last Name, First Name with the comma in the middle, so if there is no Other names, the columns has a comma.

How can I get rid of this comma?

thank you

Carson Penticuff

Can you post the formula you are using to populate the [Other Full Name] column?

OshaK

@Carson Penticuff thank you and sorry for a delay. The formula I used for [Other Full Name] is

=[Last Name]@row + ", " + [First Name]@row

The formula works fine when there is an actual name for Other Full Name, but if it's blank, it only leaves a comma hanging. is there a way to have a comma only if there is a name but remove the comma if there are other names, but no Other?

thank you.

Carson Penticuff

Try this one instead. I wasn't sure if you would ever have only the first name or last fields populated, so this accounts for that possibility as well. If they are both blank, the cell will be blank. That should eliminate the extra comma issue.

=IF(AND([Last Name]@row <> "", [First Name]@row <> ""), [Last Name]@row + "," + [First Name]@row, IF([Last Name]@row <> "", [Last Name]@row, IF([First Name]@row <> "", [First Name]@row, "")))

OshaK

@Carson Penticuff It works great, thank you!