TEXTSPLIT function in Excel

Most real-world text is delimited, with fields separated by a slash, a dash, a comma or a space. Position slicing with MID is the wrong tool the moment field lengths vary. You want to cut on the delimiter itself. The modern trio TEXTBEFORE, TEXTAFTER and TEXTSPLIT does that. They need Microsoft 365 or Excel 2024, and you won't find them in 2021 or earlier. They retired a decade of ugly LEFT(A2, FIND("-", A2)-1) nesting.

How do TEXTBEFORE, TEXTAFTER and TEXTSPLIT work?

• TEXTBEFORE(text, delimiter), everything before the first occurrence of delimiter. =TEXTBEFORE("27AABCU9603R1ZM/INV-00427", "/") → "27AABCU9603R1ZM".
• TEXTAFTER(text, delimiter): everything after it. =TEXTAFTER("27AABCU9603R1ZM/INV-00427", "/") → "INV-00427", and =TEXTAFTER("INV-00427", "-") → "00427".
• Both take an optional instance number. =TEXTBEFORE(A2, "-", 2) cuts before the second dash. A negative instance counts from the end, so =TEXTAFTER(A2, "-", -1) grabs everything after the last dash. That's the reliable way to get a file extension or a trailing serial.

TEXTSPLIT(text, col_delimiter, [row_delimiter]) is the powerhouse. It splits a string into a spilled array of cells in one shot. =TEXTSPLIT("27AABCU9603R1ZM/INV-00427", {"/","-"}) splits on both the slash and the dash, and spills three cells: 27AABCU9603R1ZM, INV, 00427. That's three fields from one formula, with no helper columns. The {"/","-"} is an array constant, and it's your first sighting of the array mindset. You handed the function a set of delimiters and it gave you back a set of results.

Pre-365 fallback (2021/2019). If TEXTBEFORE isn't available, the classic equivalents are =LEFT(A2, FIND("/", A2)-1) for "before" and =MID(A2, FIND("/", A2)+1, LEN(A2)) for "after". Know them. You'll read older workbooks full of them, and those formulas turn on one distinction. FIND is case-sensitive. SEARCH is case-insensitive and understands wildcards.

How do you join text with CONCAT and TEXTJOIN?

Joining: CONCAT and TEXTJOIN. The reverse operation assembles fields into one string.

• & and CONCAT(range) simply glue text together: ="INV-"&A2 or =CONCAT(A2:A5). CONCAT replaced the older CONCATENATE and accepts ranges.
• TEXTJOIN(delimiter, ignore_empty, text1, ...) is the one you actually want. It inserts a delimiter between items, and with ignore_empty set TRUE it skips blank cells, so you never get "Mumbai,,Pune". Take the four cells Mumbai, (blank), Pune, Nagpur. The formula =TEXTJOIN(", ", TRUE, A1:A4) returns "Mumbai, Pune, Nagpur". The empty cell vanishes and the commas stay clean. TEXTJOIN requires Excel 2019 or later.

Splitting and joining together are how you re-key data. Split a compound code into parts, fix or filter the parts, then join a new clean key you can aggregate on.