Text manipulation in Excel used to mean a chain of LEFT, MID, RIGHT, FIND, and LEN formulas with a TRIM at the end. Excel 365 added TEXTJOIN, CONCAT, TEXT, and TEXTSPLIT - the modern functions that replace the chains. The six examples below are the patterns that come up most often.
The six patterns
Build a CSV string
Old: =A1 & "," & B1 & "," & C1 & "," & D1
New: =TEXTJOIN(",", TRUE, A1:D1)
TEXTJOIN handles a range; the ampersand version requires listing every cell. The senior uses TEXTJOIN for any CSV, TSV, or pipe-delimited output.
Address from parts
Old: =A1 & ", " & B1 & ", " & C1 & " " & D1
New: =TEXTJOIN(", ", TRUE, A1:B1, C1, D1)
Mix of single cells and ranges in one call. The senior uses this pattern for the address column in any customer or vendor list.
Format a date in a label
Old: ="Date: " & A1 (returns "Date: 45678")
New: ="Date: " & TEXT(A1, "dd-mmm-yyyy")
Without TEXT, dates display as serial numbers. The senior wraps every date in TEXT in any string that goes to a human reader.
Format a currency in a label
Old: ="Total: " & A1 (returns "Total: 1234.5")
New: ="Total: " & TEXT(A1, "₹#,##0.00")
Without TEXT, currencies display without separators or symbols. The senior wraps every currency in TEXT in any string.
Clean a name field
Old: =PROPER(TRIM(A1))
New: =PROPER(TRIM(CLEAN(SUBSTITUTE(A1, " ", " "))))
The senior uses this combination on every imported name field. SUBSTITUTE removes double spaces, TRIM removes leading/trailing, CLEAN removes non-printable, PROPER capitalises correctly.
Extract the first word
Old: =LEFT(A1, FIND(" ", A1)-1)
New: =TEXTSPLIT(A1, " ") (Excel 365 only - returns an array)
For one value, LEFT/FIND is fine. For many values, TEXTSPLIT spills the result. The senior uses LEFT/FIND in legacy workbooks and TEXTSPLIT in Excel 365.
The four usage tips
TEXTJOIN with ignore_empty=TRUE
The second argument in TEXTJOIN is the ignore_empty flag. TRUE skips empty cells; FALSE includes them as empty strings. The senior uses TRUE 90% of the time - empty cells in the middle of a list should not add spurious delimiters.
TEXT every date and currency
Excel dates are serial numbers. Excel currencies are decimals. Without TEXT, they display as 45678 and 1234.5 in any string. The senior wraps every date and currency in TEXT in any string that goes to a human reader.
TEXTSPLIT for one-to-many
TEXTSPLIT splits a string by a delimiter into a vertical or horizontal array. The function spills. Combine with INDEX to get the Nth element. The senior uses TEXTSPLIT for any new work in Excel 365 and falls back to LEFT/MID/RIGHT in older versions.
TRIM + CLEAN on every import
Every imported text field has trailing spaces, non-printable characters, or inconsistent capitalisation. The senior runs TRIM + CLEAN + PROPER on every imported field, once, on day one. The cleaned data is the source for every downstream calculation.
What about REGEXMATCH, REGEXREPLACE, REGEXEXTRACT?
Excel 365 added three regex functions: REGEXMATCH, REGEXREPLACE, and REGEXEXTRACT. They handle the patterns that TEXT functions cannot - valid email, valid phone, extract domain from URL, etc. The senior uses regex for any pattern that would otherwise need a 5-step SUBSTITUTE chain. Regex is its own skill; the right time to learn it is when the first SUBSTITUTE chain gets longer than three steps.
