Standardizing strings when pressing Enter in Excel

The following article will provide detailed guidance on how to use functions to standardize strings when pressing Enter in Excel. This function will remove leading and trailing spaces, and eliminate consecutive double spaces between words. Especially, capitalizing the first letter of each word is very suitable when entering names.

Step 1: Open Microsoft Excel software.

Step 2: Press the key combination Alt + F11 -> The Microsoft Visual Basic for Application dialog box appears. Click on Insert -> Module.

Step 3: The dialogue box appears, enter the entire code snippet below:

Function FormatString(str As String) As String
Dim formattedString As String
Dim lengthOfString As Long
Dim i As Long
If Len(str) = 0 Then Exit Function
str = Trim(str)
lengthOfString = Len(str)
For i = 1 To lengthOfString
If Mid(str, i, 1) = ' ' And Mid(str, i + 1, 1) = ' ' Then
str = Replace(str, ' ', ' ')
i = i - 1
End If
Next
For i = 1 To lengthOfString
If Mid(str, i, 1) = ' ' Then
formattedString = formattedString & ' ' & UCase(Mid(str, i + 1, 1))
i = i + 1
Else
 If i = 1 Then
 formattedString = UCase(Mid(str, 1, 1))
 Else
 formattedString = formattedString & LCase(Mid(str, i, 1))
 End If
 End If
 Next
 FormatString = formattedString
End Function

Step 4: Once entered, select Save. Since the file contains a macro function, Excel will prompt whether to save the Macro. Choose Yes, then press Save to confirm the save.

Step 5: Choose the Sheet where you want to use the normalization function. Double-click on Sheet 1 as shown in the illustration.

Step 6: Choose the Change event for this Sheet. Under General, select WorkSheet, and under Declarations, select Change.

Step 7: Enter the command for the Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim string1 As String
If Not (Application.Intersect(Target, Range('$D:$D')) Is Nothing) Then
string1 = FormatString(Target.Value)
Target = string1
End If

End Sub

Note: Pay attention to the command Range('$B:$B'): column B uses the string formatting function. If you want to apply it to a different address, you can change it as follows:

- Apply from column B to column C: Range('B:$C').

- Apply from cell F9 to F15 and from H7 to H19: Range('F9:$F15, $H7:$H19').

- Apply to 2 non-contiguous columns: Range('$B:$B, $F:$F').

Step 8: Return to the Excel file. Apply to Sheet1 and column B using the normalization function.

- Enter names in column B without capitalizing the first letters and with multiple spaces between words.

- When you press Enter, it automatically capitalizes and removes excess spaces.

Wishing you all success!