If first names are in A2 and last names are in B2, enter =LEFT(TRIM(A2),1)&" "&TRIM(B2) to combine the first letter of the first name, a space, and the last name. For example, Jane in A2 and Smith in B2 returns J Smith; remove the space in the formula if you want JSmith.
The Basic Formula and What It Returns
This formula takes one character from the start of the first-name cell, then joins it to the surname. The ampersand (&) is Excel’s concatenation operator: it joins text values in the order they appear. The space inside quotation marks is a literal separator, not a cell reference.
With the first name in A2 and last name in B2, this version produces an initial and surname with no space: =LEFT(TRIM(A2),1)&TRIM(B2). For Jane Smith, the result is JSmith. If your preferred style includes a period, use this formula with the same cell layout: =LEFT(TRIM(A2),1)&". "&TRIM(B2). It returns J. Smith. Choose the version that matches the format your spreadsheet or organization needs.
LEFT returns characters from the beginning of a text value. Its second argument, 1, tells Excel to return one character. TRIM removes ordinary extra spaces at the start and end of the name, and reduces repeated spaces between words to single spaces. That helps prevent a result such as a leading blank or two spaces between the initial and surname.
Set Up the Name Columns
The simplest arrangement is one column for given names and another for surnames. For example, place First Name in A1 and Last Name in B1, then enter the formula in C2, beside the first person’s data. Keep the source columns unchanged; the formula creates a separate display value rather than editing or replacing the original names.
- Check that each row has the intended first name in column A and surname in column B.
- Select C2, or the first empty column where you want the combined result.
- Enter the formula and press Enter. Confirm the result against the source cells.
- Copy the formula down for the other rows, using the fill handle or a copy-and-paste method.
In Excel for Microsoft 365 on Windows, the formula can also be filled down by double-clicking the small square at the lower-right corner of the selected cell, provided there is adjacent data for Excel to follow. On Mac, the fill handle works similarly; keyboard shortcuts differ, so using the handle or Home > Fill > Down is a convenient alternative. Excel for the web also supports the formula and fill handle.
When the Full Name Is in One Cell
Sometimes A2 contains a complete name such as Jane Smith instead of separate first-name and surname fields. In that case, Excel needs to identify the first character and the final word. In Microsoft 365 or Excel 2024, with a full name in A2, use =IFERROR(LEFT(TRIM(A2),1)&" "&TEXTAFTER(TRIM(A2)," ",-1),""). For Jane Ann Smith, this returns J Smith because TEXTAFTER with an instance number of -1 returns the text after the last space.
This is a practical rule for names entered as words separated by spaces, but it is not a universal way to understand names. A person may have a compound surname, a suffix, a single-word name, or a naming convention that does not put the family name last. The formula treats the final space-separated word as the surname; it cannot know whether that interpretation is correct. Review the source data and confirm the intended rule before using the result for mailing, identity matching, or other important work.
TEXTAFTER is available in Microsoft 365 and Excel 2024, not every older Excel release. If your version does not recognize it, the most dependable option is to keep first and last names in separate columns, or split and verify the data before building the initial-and-surname result. Automatically splitting a full-name field can misclassify real names, so a more elaborate legacy formula is not necessarily safer.
Choose a Space, Period, or No Separator
The characters between the initial and surname are part of the output format. Excel will not insert punctuation unless the formula explicitly includes it. These are common choices:
- J Smith: use a space between the initial and surname. This is often easiest to read.
- JSmith: join the two values with no separator, which can suit compact labels or a particular username convention.
- J. Smith: include a period followed by a space when the requested style calls for an initial with punctuation.
A separator typed between quotation marks is fixed text. If a list later needs a different style, edit the formula and fill it down again. Do not add a separator to the surname source column just to make the combined result look right; keeping source data clean makes sorting, filtering, and reuse easier.
Handle Empty Cells and Extra Spaces
If either source cell is empty, the basic formula can still return part of a name or leave a separator behind. For example, a blank surname can leave an initial followed by a space. If you prefer a blank result unless both fields are present, with first names in A2 and surnames in B2, use =IF(OR(TRIM(A2)="",TRIM(B2)=""),"",LEFT(TRIM(A2),1)&" "&TRIM(B2)). With Jane in A2 and Smith in B2 it returns J Smith; if either cell is blank or contains only ordinary spaces, it returns an empty string.
An empty string is not the same as deleting the formula: the cell still contains a formula, even though it looks blank. This matters if you later count results or export the worksheet. Also, TRIM handles regular spaces but may not remove nonbreaking spaces copied from websites or other systems. If a name still appears to have odd spacing after using TRIM, inspect the source data or replace the unusual whitespace before relying on the output.
Fill the Formula Down and Keep the Results
When you copy the formula from C2 to C3, Excel adjusts the relative references to A3 and B3 automatically. This is usually what you want for a list where every row represents a different person. If the formula instead points to the same source row each time, check that the cell references were not made absolute with dollar signs, such as $A$2.
Formula results update when the source names change. If you need a fixed list for an import or handoff, copy the completed results and use Home > Paste > Paste Values. That replaces formulas in the pasted cells with their current displayed text. Keep a copy of the original data or workbook if you may need to revise the naming rule later.
For a new table with many rows in Excel, entering the formula in a table column may cause Excel to propagate it through that calculated column. Check the first few results after filling, especially if the source range has gaps or the worksheet contains unusual formatting. A quick visual check can catch shifted references or a formula entered beside the wrong row.
Common Name-Format Mistakes
The word “first” can mean the first letter of a given name, or the first character of a complete name field. If A2 contains Jane Smith and you apply LEFT to A2, the result is J; it does not extract Smith. Conversely, if A2 contains only a given name and B2 contains the surname, do not use a full-name parsing formula when the two fields are already available. Separate columns make the rule clearer and reduce guesswork.
Another frequent issue is assuming that the last word is always a person’s surname. Consider a compound surname such as De la Cruz: a last-word extraction returns Cruz, not De la Cruz. Likewise, a middle name or suffix can affect which word appears at the end. Use the full-name formula only when the data convention is known and the output has been checked against representative examples.
Finally, the initial is the first character, not necessarily the first letter in a linguistic sense. If a given name begins with punctuation, a title, or a character that should be ignored, LEFT will return that character. Clean or standardize the source field first when those cases occur. The formula performs a consistent text operation; it does not validate a person’s preferred name or determine which initial they use.
Alternatives to the Ampersand
The ampersand is a concise way to join text and works well for this task. Excel also has CONCAT, which joins text values, but it does not add spaces or punctuation automatically; you still have to provide the separator as text. The older CONCATENATE function can also join text, but the ampersand is easy to read in short formulas and makes the order of the pieces visible.
For a single initial plus surname, a formula is usually more useful than a manual edit because it stays connected to the original fields. Manual results may be appropriate for a one-time list, but changes to the source names will not flow through. If the destination system requires a specific format, confirm whether it expects a space, punctuation, or no separator before copying or exporting the results.
Frequently Asked Questions
How do I concatenate the first initial and last name without a space?
With first names in A2 and surnames in B2, use =LEFT(TRIM(A2),1)&TRIM(B2). Jane Smith returns JSmith. The ampersand joins the text directly because no separator is included.
How do I add a period after the initial?
With first names in A2 and surnames in B2, use =LEFT(TRIM(A2),1)&". "&TRIM(B2). For Jane Smith, the result is J. Smith. Change the text between the quotation marks if another punctuation style is required.
Can Excel do this if the full name is in one cell?
Yes, in Microsoft 365 or Excel 2024 you can use TEXTAFTER to take the final space-separated word, as shown above. That assumes the surname is the last word. Check compound surnames, suffixes, and single-word names rather than treating the formula’s result as guaranteed.
Why does my formula leave extra spaces?
Check for blank source cells, spaces around the names, and repeated spaces within a cell. TRIM in the formulas removes ordinary extra spaces. Nonbreaking spaces copied from external sources may remain and need to be cleaned separately.
Will the result change if I edit a name?
Yes. A formula-based result recalculates when its referenced first-name or surname cell changes. To keep a fixed text result, copy the output and use Home > Paste > Paste Values.
Related
- Excel Spreadsheet Sharing: How to Send or Collaborate
- Can You Rotate an Excel Spreadsheet? What Excel Allows
- Can You Rotate an Excel Sheet? What Actually Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FILTER Formula: Find Partial Text MatchesTo return rows where a cell contains part of a word or phrase, combine Excel’s FILTER and SEARCH…
- Excel Fill Shortcut: Fill Down, Right, and Series in CellsIn Excel for Microsoft 365 on Windows, select the cells to fill, including the source cell at the…
- Python split() Function: How to Split Strings with ExamplesUse Python’s string method split() to turn one string into a list of smaller strings. Call text.split() to…
- Merge the Contents of Two Cells in Excel with a FormulaTo combine the contents of two Excel cells without losing either value, enter a formula in a third…
- Number Format in Concatenate Excel: Keep Dates & DecimalsExcel concatenation turns numbers into text, but it does not carry over the number format shown in the…
- String Functions in JavaScript: The Essential GuideString functions in JavaScript are built-in methods that let you manipulate and inspect text values, from finding and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.