Adding space between the names in excel-CodePudding

Having an excel file as below:

I was concatenate first,middle and final name using below formula.

=CONCATENATE(A4," ",B4," ",C4)

But i need to update this formula and if there is no middle name, then the space needs to be single. and if there is a middle name.Then there should be space between both first and last name.

EXPECTED:

CodePudding user response：

If available, use TEXTJOIN() and make sure to have the 2nd parameter set to TRUE (by default), e.g.:

=TEXTJOIN(" ",,A2:C2)

You could now nest this in a BYROW() lambda helper if you feel like it (or just copy the formula down).

If TEXTJOIN() is not available, incorporate TRIM() which will get rid of double, trailing and/or leading spaces, e.g.: =TRIM(CONCATENATE(A2," ",B2," ",C1)). Please do note that CONCATENATE() in itself is redundant if one uses the ampersand to piece text together:

=TRIM(A2&" "&B2&" "&C2)

CodePudding user response：

Use an 'if' function inside your concat:

=concat(a4, " ", b4, if(b4<>"", " ", ""), c4)

CodePudding user response：

So test A4, B4 and C4 to make sure none are blank:

=if(A4="","",A4&" ")&if(B4="","",B4&" ")&if(C4="","",C4&" ")

You could just test B4 as a minimum, but being thorough saves having to go back and edit later...

Edit: Also you could take the opportunity to capitalise as necessary:

PROPER(IF(A4="","",A4&" ")&IF(B4="","",B4&" ")&IF(C4="","",C4&" "))