Skip to main content

How to concatenate first letters of names/cells in Excel?

Have you ever thought of concatenating the first name and the last name and returned only initials in Excel? This article recommends some methods to get it done easily.


Concatenate first letter/initial of first name and last name

This method will introduce the CONCATENATE function to merge only the initial letter of the first name and the last name such as AGreen representing Anne Green in Excel.

1. Select a blank cell you will place the concatenation result in, and enter the formula =CONCATENATE(LEFT(A2,1),B2) (A2 is the cell with the first name, and B2 is the cell with the last name) into it, and press the Enter key.

2. Keep selecting the concatenation result cell, and drag its AutoFill Handle to the range as you need.

And then you will see it concatenates multiple names and returns the initial letter of the first name and last name. See screenshot:

Batch combine multiple cells/rows/columns without losing data and date/number format in Excel

Normally when merging cells with the Merge feature in Excel, it will remove all cell contents except the first cell content. However, with Kutools for Excel's Combine (Rows & Columns) utility, you can easily batch combine multiple cells/rows/columns without losing data. What's more, this utility also supports remaining original date format and number formats in the combination results.


ad combine rows columns 5

Concatenate first and last names and return/get initials

Sometimes, you may want to concatenate the first name and last name and return only the initials such as A.G. in Excel. You can also apply a formula to get it done.

1. Select a blank cell you will place the concatenation result in, and enter the formula =CONCATENATE(LEFT(A2,1),".",LEFT(B2,1),".") (A2 is the cell with the first name, and B2 is the cell with the last name) into it, and press the Enter key.

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

2. Keep selecting the concatenation result cell, and drag its AutoFill handle to the range as you need.

And then you will see it concatenates multiple names and returns only the initials such as A.G. representing the full name Anne Green. See screenshot:


Concatenate first and last names and return/get initials with Kutools for Excel

You can also try Kutools for Excel’s Remove by Position utility to concatenate the names and return the initials such as A.Green or A.G. representing the full name of Anne Green.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

1. Select the proper name columns you will concatenate, and click Kutools > Text > Remove by Position.
doc concatenate first letter initials 01

Note: For returning A.Green after concatenation, please select the first name column; for returning A.G. after concatenation, please select both the first name column and the last name column. In our case, we select both of the first name column and the last name column.

2. In the opening Remove by Position dialog box, please type 999 into the Numbers box, check the Specify option and type 2 into following box, and click the Ok button. See screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

And then you will see all characters are removed except the first one in each cell of selected columns.

3. Select a blank cell you will put the concatenation initial in, and enter the formula =CONCATENATE(I2,".",J2,".") (I2 is the cell with first name, and J2 is the cell with the last name) into it, and then drag the AutoFill handle to the range as you need.
doc concatenate first letter initials 9

Note: If you remove all characters except the first letter in the First name column only, please apply this formula =CONCATENATE(I2,".",J2).

And then you will see the first names and last names have been concatenated and returned initials. See screenshot:
doc concatenate first letter initials 8


Demo: Concatenate first last names and return/get initials


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Olá
No caso de ter os dois nomes na mesma célula, e quiser unir a inicial do primeiro nome com o segundo nome, como faço? Exemplo:

Mauro Guimarães (na mesma célula) e quiser que na outra célula fique MGuimaraes
This comment was minimized by the moderator on the site
Hi there,

Let's same the cell is A1, you can use this formula: =LEFT(A1,1) & RIGHT(A1,LEN(A1)-FIND(" ",A1))
This comment was minimized by the moderator on the site
thanks for the info. how do you account for multiples of the same initials and can you write a formula to account for them? in other words, joe smith and john smyth, can you have some formula that uses a modified version of =CONCATENATE(LEFT(A2,1),".",LEFT(B2,1),"."), but also make initials for J.S.1, J.S.2 and then you can apply this to all rows?
This comment was minimized by the moderator on the site
Hi dsuspanic,
If you can add another column, this problem can be solved easily. After returning the initials column, you can apply the formula =C2&COUNTIF($C$2:$C2,C2) to combine the initial name and corresponding sequence number.
This comment was minimized by the moderator on the site
thanks for the info. how do you account for multiples of the same initials and can you write a formula to account for them? in other words, joe smith and john smyth, can you have some formula that uses a modified version of =CONCATENATE(LEFT(A2,1),".",LEFT(B2,1),"."), but also make initials for J.S.1, J.S.2 and then you can apply this to all rows?
This comment was minimized by the moderator on the site
Excelente me ayudo mucho sus instrucciones
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations