How to add space between first name and last name in Excel?
When working with spreadsheets that contain lists of names, it’s common to encounter cases where first names and last names are merged together without any spaces—such as “JohnSmith” instead of “John Smith”. This often happens due to data imports from other systems, text concatenation, or improper formatting. Such formatting not only reduces readability, but can also cause issues with sorting, filtering, and further text analysis. If you need to clearly separate first and last names in Excel, adding a space between them becomes necessary and is highly beneficial for report clarity, mail merges, and data processing in general.
The screenshot below shows an example of a name list where all first names and last names are joined together. The goal is to quickly insert a space between the first name and last name for each entry, enhancing both the visual aspect and usability of the data.
Add space between first name and last name with a Defined Function
Add space between first name and last name with Kutools for Excel 
Add space between first name and last name with a formula (Excel 2021 and later / Excel 365)
Add space between first name and last name with a Defined Function
If you need to add a space between the first and last names that are formatted as a single string, you can accomplish this with a Defined Function (also known as a User Defined Function, or UDF) in Excel. This approach is particularly helpful when the number of names is large and a manual solution would be too time-consuming. The Defined Function works by inserting a space before each uppercase letter after the first character, so it is especially suitable when your names consistently use uppercase letters to separate components (e.g., “JohnSmith”, “MaryJones”, etc.). This method automates the process, reducing manual intervention and the risk of errors.
However, consider that this method assumes that all last names start with an uppercase letter with no middle names or initials involved. For names with more complex formats, further adjustment to the function might be required.
Here’s how to create and use this Defined Function:
1. Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) window. If you’ve never used VBA before, you may need to enable the Developer tab in Excel’s ribbon for easier access.
2. In the VBA window, click Insert > Module, which opens a blank coding area. Then, copy and paste the following VBA code into the module script area.
Code: Add space between names
Function AddSpaces(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddSpaces = xOut
End Function
3. After pasting the code, press Ctrl + S to save (if prompted, save the file as a macro-enabled workbook with the .xlsm extension). Close the VBA window to return to your Excel worksheet.
Select a blank cell where you want the corrected name to appear. For example, if your name is in cell A1, type the following formula:
=AddSpaces(A1)After entering the formula, press Enter to apply it. You should see the name with the appropriate space included. To quickly process multiple names, drag the autofill handle (small square at the bottom-right corner of the selected cell) down over the range where you need to insert spaces.
Tip: This function automatically adds a space before each uppercase letter after the initial character, so for more complex name patterns (such as double-barreled names or those containing initials), adjustments to the code may be necessary.
If you encounter a #NAME? error, make sure your macro security settings allow macros to run, and that the workbook is saved as a macro-enabled file format.
Add space between first name and last name with Kutools for Excel
If writing or modifying VBA code is not your preference, Excel users can conveniently use Kutools for Excel’s Add Text utility. This intuitive tool allows you to insert a space before every uppercase letter within the selected cells, making it ideal for situations where you need a quick, non-coding solution on a wide range of data. Kutools for Excel is suitable for users who often handle large data sets and need efficient, repetitive text operations without the learning curve of scripting. Compared to manual editing, Kutools not only saves time but also reduces the risk of mistakes that may happen when processing long lists.
To use this feature, follow these steps:
1. Highlight all the cells containing names that need to be separated. In the Excel ribbon, click on Kutools > Text > Add Text.
2．In the Add Text dialog box that appears, type a space character (press the space bar) into the Text field. Then, check the Only add to option and, from the drop-down menu, choose Before uppercase letters. This setting tells Kutools to insert a space before each uppercase letter found in your names. The dialog should look like this:
3. After configuring the settings, click Ok or Apply. Kutools will immediately process the selected cells, and you’ll see that a space has been automatically added before each uppercase letter in every name:
Advantages of using Kutools: This method doesn’t require any coding skills and processes large batches of names in a few clicks. However, note that the tool will add spaces before every uppercase letter. If you have names containing middle initials or multiple uppercase characters close together, manual adjustments may be necessary afterward.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Add space between first name and last name with a formula (Excel 2021 and later / Excel 365)
As an alternative, you can use an Excel formula if each name follows a simple pattern (first name and last name, no spaces, with the last name starting with an uppercase letter). This approach is especially convenient when you can’t use VBA or add-ins and need a straightforward, formula-based solution.
1. In a blank cell adjacent to your data (for example, cell B1 if your name is in A1), enter the following formula:
=TEXTJOIN("",,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=90," "&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
This formula looks for the position of the first uppercase letter (A–Z) after the first character, splits the name at that point, and joins the parts with a space.
2. Press Enter to apply the formula. Drag the formula handle down to fill for all rows as needed. If your data starts in row 1, copy cell B1 and paste the formula to the rest of the column to process all names.
Note: This formula assumes the first and last names are merged together and the last name always starts with an uppercase letter. For names with middle initials or more complex cases, further modification to the formula may be required.
Demo: Add Spaces Between Names with Kutools for Excel
Best Office Productivity Tools
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...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in