Formula Errors

Insert a Hyphen Before the Last Four Characters in Excel

Nimra MalikNimra Malik Nov 22, 2026 868 views

Question details

Insert a hyphen before the final four characters of numeric or mixed text values in Excel.

Insert a Hyphen Before the Last Four Characters in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A column contains values such as 1234567, 4N1234, and E123456 that need a hyphen before their last four characters.
Observed behavior
The desired results are 123-4567, 4N-1234, and E12-3456.
Before you start

Identify the column containing the original values and choose an empty column for the results. If leading zeros matter, make sure the source values are stored as text; a formula cannot recover zeros that were not stored.

Solution 1Recommended

Use an Excel formula to insert the hyphen

Use LEFT and RIGHT to insert a hyphen in numeric or mixed text values, then fill the formula down the column.

1
Enter the formula

If the first value is in A2, select an empty cell such as B2 and enter =LEFT(A2,LEN(A2)-4)&"-"&RIGHT(A2,4).

2
Fill down

Press Enter, then drag the fill handle at the bottom-right corner of B2 down to apply the formula to the remaining rows.

3
Keep short values unchanged

If some values have four or fewer characters, use =IF(LEN(A2)>4,LEFT(A2,LEN(A2)-4)&"-"&RIGHT(A2,4),A2) in B2 instead, then fill it down.

Use an Excel formula to insert the hyphen
Formula results are text: The hyphenated result is text, even when the original value is numeric. Display-only leading zeros from number formatting may not be included.
WPS Spreadsheets

Add a hyphen to values in WPS Spreadsheets

Use the same LEFT, LEN, and RIGHT formula in WPS Spreadsheets to place a hyphen before the final four characters of each value.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Enter the formula: In an empty cell such as B2, enter =LEFT(A2,LEN(A2)-4)&"-"&RIGHT(A2,4), replacing A2 with the first source cell if needed.
  3. 3. Apply it to other rows: Press Enter and drag the fill handle from B2 down the column.
  4. 4. Save the workbook: Select Save or Save As to save the workbook, including the formula results.
Insert hyphens consistently in numeric and mixed text valuesOpen and edit Microsoft Excel XLSX and XLS filesFill formulas down a column for multiple rowsUse familiar spreadsheet functions in a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I use REPLACE instead of LEFT and RIGHT?

Yes. For a value in A2, enter =REPLACE(A2,LEN(A2)-3,0,"-") in an empty cell. It inserts the hyphen before the last four characters.

What happens if a value has four or fewer characters?

The basic LEFT formula may return an error for values shorter than four characters. To leave values of four or fewer characters unchanged, use =IF(LEN(A2)>4,LEFT(A2,LEN(A2)-4)&"-"&RIGHT(A2,4),A2).

Will the formula preserve leading zeros?

It preserves leading zeros stored as part of a text value. Zeros shown only by a numeric cell format may not appear in the formula result.

Can I use Flash Fill to add the hyphens?

Yes. Type an example result next to the first source value, then select the next cell and choose Data > Flash Fill. Check the results, especially if the values vary in length.