Function Problems

Split Text at the First Underscore into Two Columns in Excel

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Split a text string into a code and the remaining text at the first underscore, using exactly two columns.

Split Text at the First Underscore into Two Columns in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Extract the code

In cell B1, enter =LEFT(A1,SEARCH("_",A1)-1) and press Enter.

2
Extract the remainder

In cell C1, enter =MID(A1,SEARCH("_",A1)+1,LEN(A1)) and press Enter. Any later underscores remain in this result.

3
Apply the formulas to other rows

Select B1:C1 and drag the fill handle down alongside the source values in column A.

Use LEFT and MID to split the text into two columns
Values without an underscore: SEARCH returns an error if the source cell has no underscore. Check the source values or wrap each formula in IFERROR if you need a different result for those rows.
Split text in WPS Spreadsheets

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. 1. Open the spreadsheet: Open WPS Spreadsheets and use File > Open to select your workbook.
  2. 2. Enter the code formula: With the source value in A1, enter =LEFT(A1,SEARCH("_",A1)-1) in B1.
  3. 3. Enter the remainder formula: In C1, enter =MID(A1,SEARCH("_",A1)+1,LEN(A1)) to retain everything after the first underscore.
  4. 4. Fill down: Select B1:C1 and drag the fill handle down to apply the formulas to the other rows.
Split at the first underscore without removing later underscores.Open and save Microsoft Excel XLSX files.Apply familiar spreadsheet formulas across multiple rows.Use WPS Office for free with an easy-to-use interface.
microsoft office alternative - wps office

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.