How to create a space between first and last name in Excel?

Are you struggling to find a way to create space between first and last names in Excel? Apply the tips shared by TipsMake below! Extremely quick and easy to do.

In Excel documents, lists of first and last names are not uncommon. Normally, when we type, there will be a space between the first and last names. But there are many cases where the file is corrupted, causing this space to disappear, so the first and last names are stuck together. If you edit each cell one by one, it must be extremely tiring if that file has many different first and last names. So how to create a space between first and last name in Excel ? See TipsMake 's solution now to know the solution to this situation.

How to create a space between first and last name in Excel? Picture 1

Instructions on how to create spaces between first and last names in Excel simply and quickly

Creating spaces between letters in Excel is an interesting trick because not having spaces between first and last names is a common error. Instead of having to 'hardly' edit each data cell one by one, try applying the 2 ways below to widen the letter spacing in Excel between first name and last name!

1/ Use the Define Function to add space between first and last names in Excel

The function to insert spaces in Excel is a bit complicated and you must use the Define function if you want to create a space between the first and last names. The detailed implementation is as follows:

➽ Step 1 : The user opens the Excel you need to edit? Then press the key combination Alt + F11 to open the Microsoft Visual Basic for Application window.

➽ Step 2 : Click and select Insert > Module .

➽ Step 3 : Paste the code below to add spaces between the names.

'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)

EndIf

next

AddSpaces = xOut

End Function

How to create a space between first and last name in Excel? Picture 3

➽ Step 4 : Save the code and close the Applications window > Continue to select an empty cell and enter this formula =AddSpaces(A1) > Note, A1 changes to suit your document > Continue dragging the control box autofill control through the cells you need to apply this formula and you're done.

How to create a space between first and last name in Excel? Picture 5

2/ Use Add Text to add space between first and last name

This is also a way for users to add spaces between first and last names in Excel. The detailed implementation is as follows:

➽ Step 1 : First, you need to install the Add Text utility of Kutools for Excel HERE .

➽ Step 2 : After installation, open Excel > Click the Kutools tab > Find the Text section > Click the arrow of this line and select Add Text .

How to create a space between first and last name in Excel? Picture 7

➽ Step 3 : When the Add Text dialog box displays, you need to click on Text and enter a space in this box > Tick select Only add to and click on the arrow > Then select Before uppercase letters from the drop-down list as shown. image.

How to create a space between first and last name in Excel? Picture 9

➽ Step 4 : Click Apply and click OK to save the changes. The results are as shown.

How to create a space between first and last name in Excel? Picture 11

Instructions on how to separate the first and last names in Excel

In case it is necessary to separate the first and last names of the data, users can apply the following method:

*Separate first and last names into 2 different columns*

➽ Step 1 : For example, there is a data table as shown below > You need to highlight the cell range A2:A10 (this range can change to suit your document) > Click the Data tab in Excel's toolbar > Select ' Text to Columns '.

How to create a space between first and last name in Excel? Picture 13

➽ Step 2 : To separate characters with punctuation, you need to select Delimited and then click Next .

➽ Step 3 : At Delimiter , you need to tick Space to create space to separate characters.

How to create a space between first and last name in Excel? Picture 15

➽ Step 4 : Click Next > Click Finish to finish this process and it's complete.

NOTE

If you feel that using Text to Columns takes a lot of time because you have a lot of data. Users may consider using the LEFT function to extract certain characters from the left side of the cell. Or use the RIGHT function, to extract characters from the right side of the cell. Or you can combine both of these functions in combination with the FIND function to separate data more effectively.

Above are ways to create spaces between first and last names in Excel that TipsMake wants to share. Hope you guys do it successfully!

Micah Soto

Update 08 December 2023