How to split a code into two columns with TEXTBEFORE
A reference like AB-1200 pulled apart into its letters and its number, each into its own column.
The formula
=TEXTBEFORE(A2,"-") in column B
=TEXTAFTER(A2,"-") in column C
What each part does
TEXTBEFORE(A2,"-")- Everything up to the first dash.
TEXTAFTER(A2,"-")- Everything after it.
How it works, and what to watch for
TEXTBEFORE and TEXTAFTER read to the first dash they meet. If a code holds two dashes, the second is treated as part of what follows.
A code with no dash at all returns #N/A from both. Wrapping each in IFERROR leaves the cell blank rather than filling the column with errors.
The number that comes out is text, not a number, even though it looks like one. Wrap it in VALUE if it has to be added up or sorted numerically.
Questions
I am on Excel 2019, what do I use?
LEFT(A2,FIND("-",A2)-1) for the front, and MID(A2,FIND("-",A2)+1,99) for the back. Longer to read, same result.
Can I split on something else?
Yes. Put whatever the separator is inside the quotes: a space, a slash, a comma.
What about Text to Columns?
It does the job once, as a one-off edit. A formula keeps working as new rows arrive.
The example sheet
| Code |
Prefix |
Number |
| AB-1200 |
|
|
| CD-3400 |
|
|
| EF-5600 |
|
|
If this helped, these are close by
More on TEXTBEFORE, and every walkthrough that uses it.
