When joining parts of names together, you may want vary the characters you use to separate the name. Depending on the effect you may be able to use array syntax to control the characters and their location.
In the image below we have three separate names.
We want to join the three names to create a single string with Last,First Middle. We want the comma between Last and First and a space between First and Middle.
You can use array syntax with TEXTJOIN to vary the characters used to join the names.
The formula in cell D2 is shown below and has been copied down.
=TEXTJOIN({","," "},1,C2,A2,B2)
In array syntax the curly brackets (braces) surround the text to use to separate the text. The array syntax instructs Excel to use the comma first and the space second.
The 1 as the second argument in the TEXTJOIN function instructs Excel to ignore empty (blank) cells.
Just noting that a shorter formula that uses no function calls and can spill the entire result without needing to be copied down can be constructed using a legacy concept and, of course, dynamic arrays…
=C2:C5&”,”&A2:A5&” “&B2:B5
Thanks Rick
As always a shorter, more elegant solution.
I was trying to showcase the use of the array syntax in TEXTJOIN with this example.
One thing you might like to highlight is that if there are more items to join than delimiters in specified in the array of delimiters, then the specified delimiters are reused cyclically. For example, put this in cells A1 to A6…
one
two
three
four
five
six
and put this formula in some other cell and note how the delimiters are distributed…
=TEXTJOIN({“,”,”-“},,A1:A6)
Hi Rick
Thanks for the suggestion.