Two columns into one: a first name and a surname become a full name, or a couple of codes become a single key. This joins them with whatever you like in between.
The formula
=TEXTJOIN(" ",TRUE,A2,B2) in column C
What each part does
" "
What to put between the pieces, here a single space. Use ", " for a comma, or "" for nothing at all.
TRUE
Skip empty cells instead of leaving a double separator.
A2, B2
The cells to join, in order. Add as many as you like, each separated by a comma.
How it works, and what to watch for
The ampersand glues text together. Putting " " between the two parts adds the space that keeps the words from running into each other.
For many pieces at once, TEXTJOIN is cleaner, since you set the separator once rather than repeating it.
If a number loses its formatting when joined, wrap it in TEXT to spell out how it should look.
Questions
How do I add a comma or a dash between them?
Put it in quotes between the two parts: =A2&", "&B2. TEXTJOIN is neater when there are several pieces.
Why did my date turn into a number when I joined it?
Joining strips the display format. Wrap the date in TEXT, like TEXT(A2,"dd/mm/yyyy"), to keep it looking like a date.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
