How to Insert Hyphens into Alphanumeric Excel IDs
Question details
The user needs to format alphanumeric identification codes in Excel by adding hyphens at specific intervals without altering the original letters and numbers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting raw, continuous strings of identification codes or serial numbers into a readable, hyphenated grouping format.
- Observed behavior
- The IDs currently appear as continuous text strings and need to be separated into distinct character blocks (e.g., groups of three, three, four, and remaining characters) using hyphens.
Ensure your alphanumeric codes do not contain leading or trailing spaces, as this can disrupt the fixed-position string extraction formula.
Use TEXTJOIN and MID Functions to Insert Hyphens
Extract specific character groups using the LEFT and MID functions, then combine them automatically with hyphens using TEXTJOIN.
This formula separates a continuous alphanumeric ID into specific groupings without deleting any original characters. It works by targeting the exact starting position and length of each character block.
Click on an empty cell next to your first unformatted ID (for example, click cell B2 if your data begins in cell A2).
Type the formula =TEXTJOIN("-",TRUE,LEFT(A2,3),MID(A2,4,3),MID(A2,7,4),MID(A2,11,100)) into the formula bar and press Enter.
Select cell B2 again, click and hold the fill handle (the small square at the bottom-right corner), and drag it down to format the rest of your alphanumeric IDs.
Use WPS Spreadsheet to Format Alphanumeric IDs
WPS Spreadsheet fully supports advanced text functions like TEXTJOIN, LEFT, and MID, allowing you to easily format complex identification codes. It offers a familiar interface and comprehensive data processing tools for free.
- 1. Open your spreadsheet in WPS Office: Launch WPS Office and open your workbook containing the alphanumeric IDs that need to be formatted.
- 2. Apply the extraction formula: Select a blank cell adjacent to your data and input =TEXTJOIN("-",TRUE,LEFT(A2,3),MID(A2,4,3),MID(A2,7,4),MID(A2,11,100)) in the formula bar.
- 3. Drag to fill the column: Press Enter to apply the formula, then drag the fill handle at the bottom right corner of the active cell downward to format your entire list.
Frequently Asked Questions
Can I use the REPLACE function to insert hyphens instead of TEXTJOIN?
Yes, you can nest multiple REPLACE functions to add hyphens. For example, using =REPLACE(REPLACE(A2,4,0,"-"),8,0,"-") inserts a hyphen at the 4th and 8th positions without replacing or losing any existing characters.
Why is the TEXTJOIN function resulting in a #NAME? error?
The TEXTJOIN function is only available in Excel 2019 and later, or Microsoft 365. If you are using an older version, Excel will return a #NAME? error. You can use the ampersand (&) operator as an alternative.
How do I use the ampersand (&) to add hyphens to my IDs?
You can manually combine extracted text strings and hyphens using the & symbol. For example, use the formula: =LEFT(A2,3) & "-" & MID(A2,4,3) & "-" & MID(A2,7,4) & "-" & MID(A2,11,100).
