Formatting Issues

How to Add Dashes to Alphanumeric Codes in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to insert dash separators into 12-character text strings (e.g., formatting AUMEV2027833 as AA-BBB-00-00000) since default formatting tools only work on numerical values.

Product: Excel
Device & OS: not provided
Scenario: Reformatting a column of mixed alphanumeric codes into a standardized hyphenated format for better readability and data consistency.
Observed behavior: Standard Excel custom number formatting fails to apply separators to text values containing letters, leaving the alphanumeric codes completely unformatted.

Before you start

Verify that all alphanumeric codes in your dataset have a consistent length (e.g., exactly 12 characters) to ensure the formula inserts dashes in the correct positions without cutting off data.

Solution 1Recommended

Use Text Functions in a Helper Column

By combining LEFT, MID, and RIGHT functions, you can extract specific characters from a text string and concatenate them with dashes.

Because standard custom formats do not work on text strings containing letters, using a formula is the most robust way to parse the string. This method allows you to dynamically place dashes anywhere within the cell's contents.

Set up a helper column

Select an empty cell in a column adjacent to your alphanumeric codes (for example, select cell K2 if your data begins in cell J2).

Enter the concatenation formula

Type the formula =LEFT(J2,2)&"-"&MID(J2,3,3)&"-"&MID(J2,6,2)&"-"&RIGHT(J2,5) into the formula bar and press Enter.

Apply formatting to the remaining rows

Click the newly formatted cell, then double-click or drag the fill handle at the bottom-right corner to copy the formula down to the rest of the column.

Replace original data with static values

Select the newly generated hyphenated codes, press Ctrl+C to copy them, right-click the original data column, and select Paste Special > Values. You can then delete the helper column.

Handling Mixed Data Types: If your column contains both alphanumeric strings and pure numbers that you want to leave unchanged, wrap the statement in an IF function: =IF(ISNUMBER(J2),J2,LEFT(J2,2)&"-"&MID(J2,3,3)&"-"&MID(J2,6,2)&"-"&RIGHT(J2,5))

Solution 2

Format Cells Using a VBA Macro

A custom VBA script can insert dashes directly into the selected cells without the need for temporary helper columns.

Advanced Data Formatting

Format Alphanumeric Data Seamlessly in WPS Spreadsheet

You can easily format complex text strings, utilize advanced string manipulation functions, and manage large datasets using WPS Spreadsheet, a fully compatible and highly efficient alternative to Microsoft Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the alphanumeric codes you need to reformat.
2. Input the formatting formula: In a blank adjacent column, enter the exact formula: =LEFT(J2,2)&"-"&MID(J2,3,3)&"-"&MID(J2,6,2)&"-"&RIGHT(J2,5).
3. Fill the formula down: Drag the fill handle downward to instantly apply the dash formatting to all rows in your dataset.
4. Paste Special as Values: Copy the new column, right-click over your original data, and choose Paste Special > Values to remove the formula dependency.

100% compatibility with Microsoft Excel text formulas like LEFT, MID, and RIGHTLightweight design for faster processing of large datasets without crashingFree built-in features for advanced data formatting and pasting as values

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't standard custom cell formatting work for alphanumeric codes?

Excel's custom number formatting (such as 00-000-00-00000) is designed strictly for numerical values. Once a cell contains a letter, Excel treats the entire cell as text, rendering standard number formatting rules completely ineffective.

How do I remove the dashes if I need to revert to the original alphanumeric code?

You can use the SUBSTITUTE function in a new helper column to strip the dashes. Simply use the formula =SUBSTITUTE(J2, "-", "") to replace all hyphens with a blank string, effectively returning the code to its original state.

Will this formula work if my codes have varying lengths?

No. The combination of LEFT, MID, and RIGHT in this specific formula is hardcoded for a 12-character string. If your strings vary in length, you will need a more complex formula incorporating the LEN or SEARCH functions, or a VBA script, to identify where the dashes should go dynamically.

Document & File Issues