Split and Combine Text: TEXTSPLIT, TEXTJOIN, LEFT, RIGHT, MID
Combine
=A2 & " " & B2
=TEXTJOIN(", ", TRUE, A2:D2)TEXTJOIN skips blanks when the second argument is TRUE.
Split
- Excel 365:
=TEXTSPLIT(A2, " ") - Google Sheets:
=SPLIT(A2, " ") - Menu option in both: Data > Text to Columns (Excel) or Data > Split text to columns (Sheets).
Extract parts
- First 3 characters:
=LEFT(A2, 3) - Last 4:
=RIGHT(A2, 4) - First name:
=LEFT(A2, FIND(" ", A2)-1) - Text after the first space:
=MID(A2, FIND(" ", A2)+1, 100)
Clean up
TRIM removes extra spaces, PROPER capitalises each word, and UPPER and LOWER change case.