Insert a Hyphen Before the Last Four Characters in Excel
Question details
Insert a hyphen before the final four characters of numeric or mixed text values 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.
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.
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.
If the first value is in A2, select an empty cell such as B2 and enter =LEFT(A2,LEN(A2)-4)&"-"&RIGHT(A2,4).
Press Enter, then drag the fill handle at the bottom-right corner of B2 down to apply the formula to the remaining rows.
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.

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. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 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. Apply it to other rows: Press Enter and drag the fill handle from B2 down the column.
- 4. Save the workbook: Select Save or Save As to save the workbook, including the formula results.

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.




