Function Problems

How to Create an Employee ID from Text Fields Using Excel Formulas

Aamir Naveed Akram Sep 30, 2026 869 views

Question details

The user needs an Excel formula to generate unique employee IDs by extracting and combining specific characters from adjacent columns: Region, EmployeeID, FullName, and PositionID.

How to Create an Employee ID from Text Fields Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Generating customized text strings for identifiers by pulling specific substrings from multiple data columns and applying the rule down the entire worksheet.
Observed behavior: The goal is to properly extract a defined number of characters from different text fields and concatenate them into a single string in a new column.

Before you start

Ensure your data columns are properly aligned and contain no accidental leading or trailing spaces, as invisible spaces will be counted by character extraction functions and may skew your final generated IDs.

Solution 1Recommended

Use the Ampersand (&) Operator with LEFT and RIGHT Functions

This solution uses text string functions to extract characters from the edges of specific text cells and joins them into one continuous ID string.

The LEFT function extracts a given number of characters starting from the beginning of a text string, while the RIGHT function extracts characters starting from the end. By combining these with the ampersand (&) operator, you can piece together custom identifiers.

Select the target cell

Click on cell H3 (or whichever cell you want to display the first generated Employee ID).

Input the concatenation formula

Type the formula: =A3&RIGHT(B3,2)&LEFT(C3,3)&RIGHT(D3,4). Note: Adjust the cell references (A3, B3, C3, D3) to accurately correspond to your Region, EmployeeID, FullName, and PositionID columns for that row.

Apply the formula

Press Enter on your keyboard to generate the combined text string.

Copy the formula down the column

Hover your cursor over the bottom-right corner of cell H3 until the Fill Handle (a small black plus sign) appears. Click and drag it down the worksheet to automatically apply the formula to the remaining employees.

Use the Ampersand (&) Operator with LEFT and RIGHT Functions

Using CONCATENATE Instead: If you prefer function names over symbols, you can achieve the exact same result using the CONCAT or CONCATENATE function: =CONCATENATE(A3, RIGHT(B3,2), LEFT(C3,3), RIGHT(D3,4)).

Efficient Spreadsheet Tool

Seamlessly Combine Text and Data with WPS Spreadsheet

WPS Spreadsheet fully supports standard Excel text formulas like LEFT, RIGHT, and concatenation operators, allowing you to easily manage and manipulate text fields for generating custom IDs.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your employee records.
2. Input your text formula: Select the target cell and input your combination formula using the standard LEFT and RIGHT functions along with the ampersand operator.
3. Fill down the column: Press Enter to view the result, then use the drag-fill handle in the bottom-right corner to copy the formula for all other entries.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Built-in text tools and features for quick data formatting and substring extraction.Free to use with a familiar, tabbed user interface.Lightweight software that runs smoothly on low-spec devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my LEFT or RIGHT formula include blank spaces instead of letters?

Your original text cells might contain hidden leading or trailing spaces. You can wrap your cell references in the TRIM function, such as =LEFT(TRIM(C2), 3), to automatically remove unwanted spaces before extracting the characters.

Can I add dashes or symbols between the combined text fields?

Yes, you can insert specific characters by enclosing them in quotation marks and adding them to your formula with the ampersand operator. For example: =A2 & "-" & RIGHT(B2,2) will insert a hyphen between the first two fields.

How do I extract text up to a specific character like a space?

You can use the FIND or SEARCH function inside the LEFT function to extract text up to a specific character. For example, =LEFT(C2, FIND(" ", C2)-1) will extract only the first name from a full name cell.

What happens if a cell has fewer characters than the RIGHT function asks for?

If the text string is shorter than the number of characters specified in the formula, the LEFT or RIGHT function will simply return the entire contents of that cell without causing an error.

Excel & Data Problems