How to Convert Last Names to Uppercase and First Names to Proper Case in Excel
Question details
The user needs to format a full name entered as "last name, first name" so the surname is uppercase, the given name is proper case, and the comma is preserved.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Clean up or standardize a name list where names are stored in a single cell in Lastname, Firstname order.
- Observed behavior
- Names such as "presti, christine" display with inconsistent capitalization and need formula-based case conversion.
Confirm the names are in a consistent "Lastname, Firstname" format in one column, and insert an empty helper column next to the source data for the formula results.
Use UPPER, PROPER, LEFT, MID, and FIND to reformat the name
Recommended when names are consistently separated by a comma and you want one formula that converts the last name to uppercase and the first name to proper case while keeping the comma.
The formula splits the cell at the comma: LEFT returns the surname, MID returns the given name, UPPER converts the surname to uppercase, and PROPER converts the given name to proper case. The comma is preserved by concatenating it between the two parts.
Click an empty cell in the same row as the first name, for example B2.
Type =UPPER(LEFT(A2,FIND(",",A2)-1))&PROPER(MID(A2,FIND(",",A2)+1,LEN(A2))) and press Enter.
Select the formula cell and drag the fill handle down through the remaining name rows to apply the conversion.
Convert Name Case in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same UPPER, PROPER, LEFT, MID, and FIND functions used in Excel, so you can build this name-case formula without changing your workflow.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx workbook containing the name list.
- 2. Select the helper cell: Click the cell next to the first name where the formula result should appear.
- 3. Enter the formula: Type =UPPER(LEFT(A2,FIND(",",A2)-1))&PROPER(MID(A2,FIND(",",A2)+1,LEN(A2))) and press Enter.
- 4. Fill down: Drag the fill handle to apply the formula to the remaining names.
Frequently Asked Questions
What does the formula do if the name has no comma?
The FIND function returns an error, so the formula depends on the "Lastname, Firstname" pattern. Use an IFERROR wrapper or split names into separate columns if the format is inconsistent.
Can this formula handle middle names or initials?
Yes, the MID and PROPER part converts everything after the comma to proper case, so a middle initial or second given name is also capitalized properly as long as it is after the comma.
Will this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports UPPER, PROPER, LEFT, MID, FIND, and LEN with the same syntax as Excel.
How do I keep the formula results instead of formulas?
Select the result cells, copy them, then use Paste Special > Values to replace the formulas with static text.
