Normalize string when pressing Enter key in Excel

The following article will guide in detail how to use functions to normalize strings when pressing the Enter key in Excel. This function will remove spaces at the beginning and end of the line, delete two consecutive white space characters between words. Especially capitalizing the first letter in each word this is very suitable when entering the name field.

Step1: Open the Microsoft Excel software .

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

Normalize string when pressing Enter key in Excel Picture 1

Step 3: A dialog box appears, enter the following code:

Normalize string when pressing Enter key in Excel Picture 2

Function Chuanhoachuoi (str As String) As String Dim sChoioi As String Dim mlen As Long Dim i As Long If Len (str) = 0 Then Exit Function str = Trim (str) mlen = Len (str) For i = 1 To mlen 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 mlen If Mid (str, i, 1) = "" Then sChuoi = sChuoi & "" & UCase (Mid (str, i + 1, 1)) i = i + 1 Else If i = 1 Then sChuoi = UCase (UCase ( Mid (str, 1, 1)) Else sChuoi = sChuoi & LCase (Mid (str, i, 1)) End If End If Next Chuanhoachuoi = sChuoi End Function

Step 4: After importing, select Save . Because the file contains a macro function, when saving Excel a message to save the Macro, you choose Yes . Then click Save to save.

Normalize string when pressing Enter key in Excel Picture 3

Step 5: Select Sheet using normalization function. Double click on Sheet 1 as shown.

Normalize string when pressing Enter key in Excel Picture 4

Step 6: Select the Change event for this Sheet. Under General select WorkSheet , section Declarations select Change .

Normalize string when pressing Enter key in Excel Picture 5

Step 7: Enter the command for the Change event .

Normalize string when pressing Enter key in Excel Picture 6

 Private Sub Worksheet_Change (ByVal Target As Range) Dim str1 As String If Not (Aplication.Intersect (Targer, Range ("$ D: $ D")) Is Nothing) Then str1 = Chuanhoachuoi (Target.Value) Target = str1 End If End Sub 

Note: Note the Range statement ("$ B: $ B") : column B uses the string normalization function. If you want to apply with another address can change as follows:

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

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

- Apply 2 non-consecutive columns: Range ("$ B: $ B; $ F: $ F") .

Step 8: Return to the Excel file. Applies to Sheet1 and column B using the standardized function.

- In column B enter your first name without capital letters first and between 2 words with many spaces.

Normalize string when pressing Enter key in Excel Picture 7

- When you press Enter automatically capitalizes and removes extra spaces.

Normalize string when pressing Enter key in Excel Picture 8

Good luck!