Function Problems

Split Text at a Specific Character into Two Columns in Excel

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Split each cell in column A at the first occurrence of “1.” and move the marker and following text to column B.

Split Text at a Specific Character into Two Columns in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains text in column A that needs to be separated into two columns at a specific marker.
Observed behavior
The desired result is text before the first “1.” in column A and text beginning with “1.” in column B.
Before you start

Check that columns B, C, and D are available for the results and temporary formulas. The instructions assume your data starts in A2; adjust the cell references if it starts elsewhere.

Solution 1Recommended

Split at the first marker using Excel formulas

Use formulas compatible with older and newer Excel versions, then paste the results into columns A and B.

The formulas split at the first exact occurrence of “1.” and retain that marker in the second result. If the marker is absent, the original text stays in the first result and the second result is blank.

1
Extract the text before the marker

In C2, enter =IFERROR(LEFT(A2,FIND("1.",A2)-1),A2).

2
Extract the marker and following text

In D2, enter =IFERROR(MID(A2,FIND("1.",A2),LEN(A2)),"").

3
Fill the formulas down

Select C2:D2 and drag the fill handle down through the last row of data in column A.

4
Place the results in columns A and B

Select the filled cells in columns C and D and copy them. Select A2, then use Home > Paste > Paste Special > Values to replace the original text with the two results.

5
Remove the temporary formulas

After checking columns A and B, select the temporary cells in columns C and D and clear their contents.

Split at the first marker using Excel formulas
Spaces are preserved: Any spaces immediately before or after the marker remain in the extracted text.
Split text in WPS Spreadsheets

Split text at a marker with WPS Spreadsheets

Use familiar spreadsheet formulas in WPS Spreadsheets to separate text at the first “1.” while keeping the marker in the second column.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and choose Open to select your workbook. Make sure columns B through D are available.
  2. 2. Enter the first formula: For data starting in A2, enter =IFERROR(LEFT(A2,FIND("1.",A2)-1),A2) in C2.
  3. 3. Enter the second formula: In D2, enter =IFERROR(MID(A2,FIND("1.",A2),LEN(A2)),"").
  4. 4. Fill and copy the results: Select C2:D2, drag the fill handle down through your data, and copy the filled cells.
  5. 5. Paste values into the target columns: Select A2 and use Paste Special > Values to place the results in columns A and B. Check the results, then clear the temporary formulas in columns C and D.
Keep the marker and following text together in the second columnOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and copy-and-paste toolsWork with spreadsheets using the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if a cell does not contain “1.”?

The IFERROR formulas return the original text in the first result and a blank in the second result.

How can I split at a different marker?

Replace each occurrence of "1." inside the FIND formulas with your chosen marker, keeping the quotation marks.

Will the formulas split at every occurrence of the marker?

No. FIND locates the first occurrence, so any later occurrences remain in the second result.

Why should I paste the results as values?

The formulas refer to column A. Use Home > Paste > Paste Special > Values so the extracted text replaces the original cells without retaining those references.