Excel Formulas to Shorten Names Without Duplicate Initials
Question details
Create shortened names in Excel without producing duplicate results for people with the same last name and first initial.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains last names and first names that need to be displayed in a concise, distinguishable format.
- Observed behavior
- Using only the first initial can produce the same result, such as Smith, J., for both Smith, Jane and Smith, Joe.
Place last names in column C and first names in column D, starting in row 3. The examples check rows 3 through 100; adjust the ranges to include every name in your list.
Use helper columns to extend duplicate initials in Excel
Use COUNTIF to identify duplicate abbreviations and add first-name characters only where needed.
In cell E3, enter =C3&", "&LEFT(D3,1)&"." and fill the formula down through the last name in your list.
In F3, enter =IF(COUNTIF($E$3:$E$100,E3)>1,C3&", "&LEFT(D3,2)&".",E3) and fill it down. Smith, Jane and Smith, Joe become Smith, Ja. and Smith, Jo.
In G3, enter =IF(COUNTIF($F$3:$F$100,F3)>1,C3&", "&LEFT(D3,3)&".",F3) and fill it down. This extends any abbreviations that still match in column F.
Check column G for duplicates with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. If needed, add another helper column using the same IF and COUNTIF pattern, referring to column G and changing LEFT(D3,3) to LEFT(D3,4).

Create distinct name abbreviations in WPS Spreadsheet
Use helper columns and COUNTIF in WPS Spreadsheet to extend duplicate initials while keeping other names short.
- 1. Open your worksheet: Open the workbook in WPS Spreadsheet and place last names in column C and first names in column D, starting at row 3.
- 2. Add initial abbreviations: Enter =C3&", "&LEFT(D3,1)&"." in E3, then drag the fill handle down the list.
- 3. Extend matching initials: Enter =IF(COUNTIF($E$3:$E$100,E3)>1,C3&", "&LEFT(D3,2)&".",E3) in F3 and fill it down. Adjust the range if your list extends beyond row 100.
- 4. Resolve remaining matches: Enter =IF(COUNTIF($F$3:$F$100,F3)>1,C3&", "&LEFT(D3,3)&".",F3) in G3 and fill it down. Add further helper columns if matches remain.

Frequently Asked Questions
Why do Smith, Jane and Smith, Joe both become Smith, J.?
A formula using LEFT(D3,1) takes only the first letter of each first name. Use COUNTIF on the abbreviation column to find matches, then use LEFT(D3,2) for those names.
How can I highlight duplicate abbreviations in Excel?
Select the abbreviation cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
What if two first names share their first two letters?
Add another helper column that checks the two-letter results with COUNTIF and uses LEFT with three characters for duplicates. Repeat with more characters if necessary.
Can the formula distinguish two people with identical names?
Not from identical first and last names alone. Add another field, such as a middle initial or employee ID, to distinguish those records.




