extra space and a comma to remove

OshaK

I'm using this formula to combine Last, First Names with the Middle name initial (if it exists) and the credentials:

=[Last Name]@row + ", " + [First Name]@row + " " + [Middle Initial]@row + ", " + [Degree(s)]@row

If there is a Middle name initial, there is no problem and it displays fine. However, if there is no initial, I get the extra space and a comma. Is it possible to make this formula conditional and ONLY display the extra space and comma when there is an initial?

Thank you

James Hokamp

Hi @OshaK,

You should be able to do this with an IF statement.

Let me know if this helps!

James

OshaK

thank you so much, @James Hokamp! Any chance you can copy/paste the formula to this conversation? Also, I'm sorry, I need one more condition for the Degrees column because not everyone has the degrees listed. So the commas and spaces should only appear if there is either an initial or degrees or both, and no spaces and commas if none is present, and a single comma, space if one of them is present.

Thank you!

Andrée Starå

Hi @OshaK

I hope you're well and safe!

We could use JOIN COLLECT, but would it work if we moved the columns around so they would be?

Last Name, First Name, Middle Initial, Degree(s)

Is that an option?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

L_123

@Andrée Starå

A substitute join would be cleaner i think.

if the columns are arranged as you said, Last Name, First Name, Middle Initial, Degree(s)

=substitute(join([Last Name]@row:[Degree(s)]@row,", "),", ,",",")

vs

=join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")

OshaK

this is great, thank you, @Andrée Starå , it's perfect!

OshaK

@Andrée Starå, thank you again, it works great, except when there is no initial nor degrees.

When there are just last and first names, there is an extra comma. Is there a way to remove it automatically if there are only last and first names?

so instead of

Doe, Osha,

can it be

Doe, Osha

?

thank you!

L_123

Then @Andrée Starå's initial suggestion would be optimal. I assumed you were forcing the user to enter those values.

=join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")

OshaK

@L_123 thank you, that works! How would I develop this formula further so I can have a break line?

I need it to look like that:

Last Name, First Name

Degree 1, Degree2

Degree 3

(I need it to generate the document, where the formatting is important. Thank you!

L_123

you need to separate it out into 3 separate statements and use char(10) between them