Split Text at the First Underscore into Two Columns in Excel
Question details
Split a text string into a code and the remaining text at the first underscore, using exactly two columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains values such as AB_first_second, and the user wants the code in one column and everything after the first underscore in another.
- Observed behavior
- The desired result is AB in the first output column and first_second in the second, with later underscores preserved.
Identify the column containing the source text and make sure two adjacent output columns are available. The formulas below assume the first source value is in A1 and contains an underscore.
Use LEFT and MID to split the text into two columns
Use these formulas in current or older Excel versions to split at the first underscore and retain the full remainder.
In cell B1, enter =LEFT(A1,SEARCH("_",A1)-1) and press Enter.
In cell C1, enter =MID(A1,SEARCH("_",A1)+1,LEN(A1)) and press Enter. Any later underscores remain in this result.
Select B1:C1 and drag the fill handle down alongside the source values in column A.

Use a single dynamic array formula in Microsoft 365
Use one formula to return both parts into adjacent cells when your Excel version supports HSTACK, TEXTBEFORE, and TEXTAFTER.
Split text at the first underscore in WPS Spreadsheets
Use LEFT, SEARCH, and MID in WPS Spreadsheets to place the code and the remaining text in two columns, preserving later underscores.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use File > Open to select your workbook.
- 2. Enter the code formula: With the source value in A1, enter =LEFT(A1,SEARCH("_",A1)-1) in B1.
- 3. Enter the remainder formula: In C1, enter =MID(A1,SEARCH("_",A1)+1,LEN(A1)) to retain everything after the first underscore.
- 4. Fill down: Select B1:C1 and drag the fill handle down to apply the formulas to the other rows.

Frequently Asked Questions
Will additional underscores create more columns?
No. The LEFT and MID formulas use the position of the first underscore and return only two results. For example, AB_first_second becomes AB and first_second.
What happens if a cell has no underscore?
SEARCH returns an error. To show the original text as the first result instead, use =IFERROR(LEFT(A1,SEARCH("_",A1)-1),A1) in B1 and =IFERROR(MID(A1,SEARCH("_",A1)+1,LEN(A1)),"") in C1.
Do the formulas require the code to be exactly two letters?
No. LEFT extracts all characters before the first underscore, so the code can have any length.
Why does the HSTACK formula show a spill error?
The formula needs two adjacent output cells. Clear any content from the cell immediately to the right of the formula and try again.




