Create an Eight-Character Excel Username from First and Last Names in WPS Office
Question details
The user wants to generate a lowercase eight-character username by combining the first initial and last name while removing spaces, hyphens, and periods.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is preparing a list of usernames in a worksheet and needs a formula that standardizes first and last name values into a lowercase eight-character identifier.
- Observed behavior
- The desired result is a lowercase username built from the first initial and the last name, with spaces, hyphens, and periods removed and the final text limited to eight characters.
Open WPS Office Spreadsheets and ensure the first names are in one column, for example column A, and the last names are in another column, for example column B. If your data uses different cell references, adjust the formula references accordingly.
Use a Nested SUBSTITUTE Formula to Generate the Username
This is the most direct solution when you need a single formula that cleans, concatenates, lowercases, and truncates the username in one step.
The formula uses LOWER to lowercase the combined value, SUBSTITUTE to strip spaces, hyphens, and periods from the last name, LEFT to take only the first initial from the first name, and then a second LEFT to limit the final result to eight characters.
Click the cell where you want the eight-character username to appear, for example C1.
Type =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1,1)&B1),"-","")," ",""),".",""),8) and press Enter.
Drag the fill handle from the lower-right corner of the result cell down through the remaining rows to create usernames for the rest of your list.
Use the LET Function for a Clearer Formula
Use this version if you prefer a more readable formula that avoids repeating the SUBSTITUTE chain.
Build a Standardized Excel Username Formula in WPS Office
You can complete this task directly in WPS Office Spreadsheets by using Excel-compatible text functions such as LEFT, LOWER, and SUBSTITUTE. Enter the formula, fill the column, and WPS will calculate the lowercase eight-character usernames instantly without extra tools.
- 1. Download WPS Office: Download and install WPS Office, then open WPS Spreadsheets.
- 2. Prepare your name columns: Place first names in column A and last names in column B, starting from row 1.
- 3. Enter the username formula: In cell C1, enter the username formula =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1,1)&B1),"-","")," ",""),".",""),8).
- 4. Apply it down the column: Drag the fill handle down to generate the eight-character username for every row.
Frequently Asked Questions
How does this formula remove spaces, hyphens, and periods from the last name?
The SUBSTITUTE function is nested three times: it first replaces hyphens with an empty string, then spaces, then periods, so the remaining last name contains only letters and numbers before it is combined with the first initial.
Can I change the cell references if my first and last names are in different columns?
Yes. Replace A1 with the first-name cell reference and B1 with the last-name cell reference in the formula. For example, if first names are in D2 and last names are in E2, use LEFT(D2,1)&E2 inside the formula.
Why does the formula use LEFT twice?
The first LEFT extracts the first character of the first name to create the initial. The second LEFT limits the final combined username to eight characters after the text has been cleaned and lowercased.
Does this formula work as an Excel-compatible formula in WPS Office?
Yes, WPS Office Spreadsheets supports standard Excel text functions including LEFT, LOWER, SUBSTITUTE, and LET, so the formula works in WPS Office and Microsoft Excel.
What can I do if some names already repeat or exceed eight characters?
The formula always truncates to eight characters, but duplicates can still occur if two names produce the same result. You can add an ID number or use a COUNTIF helper column to detect and resolve duplicate usernames.
