Normalize String When Pressing Enter Key in Excel

This article provides a clear overview of Normalize String When Pressing Enter Key in Excel, with the main facts, useful context, and practical details organized for easy reading.

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 .

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

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.

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

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

Step 7: Enter the command for the Change event.

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 may need 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.

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