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!

4 ★ | 1 Vote

May be interested

  • String (String) in C / C ++String (String) in C / C ++
    this form of string originates from c language and continues to be supported in c / c ++. the string in the c programming language is essentially a one-dimensional array of characters that ends with a null character ''.
  • Instructions on how to count words in cells in ExcelInstructions on how to count words in cells in Excel
    in many cases you want to count the number of characters in a text string (text string) to calculate or combine with other functions, or to do something. you can then use the trim function, combining the len function, the substitute function. using the trim function to remove spaces, use the substitute function to replace the current text with new text in a text string, ...
  • string.h in Cstring.h in C
    the file header named string.h in c library defines a variable type, a macro and various functions to manipulate character arrays.
  • How to insert characters at the beginning or end of a string in ExcelHow to insert characters at the beginning or end of a string in Excel
    recently software tips received many questions asking how to insert characters at the beginning or end of a string in excel. so today, dexterity software introduces readers how to insert more characters into the string in this article, invite you to track.
  • MID and MIDB functions to cut strings in ExcelMID and MIDB functions to cut strings in Excel
    the mid () function returns a string from a text string, with the position to start extracting from the text string and the number of characters returned by the string that is specified. functions used in languages ​​use a single-byte character set (sbcs) and always count each character as 1.
  • How to use Excel's VALUE functionHow to use Excel's VALUE function
    excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
  • What is ### error in Excel? how to fix ### error in ExcelWhat is ### error in Excel? how to fix ### error in Excel
    ### in excel is an error that excel frequently encounters and appears in many different cases when we enter data in excel, functions in excel, enter formulas in excel or when displaying results.
  • What is the pound sign error (####) in Excel?What is the pound sign error (####) in Excel?
    #### in excel is a frequently encountered excel error and appears in many different cases when we enter data in excel, functions in excel, and enter formulas in excel.
  • How to automatically wrap lines in Excel (Wrap Text in Excel)How to automatically wrap lines in Excel (Wrap Text in Excel)
    the line break in excel is not as simple as pressing enter as in ms word software. today, dexterity software will guide you to the warp text tool to automatically wrap lines that match the width of the column!
  • Split numbers from strings in ExcelSplit numbers from strings in Excel
    in the process of working with excel, i have encountered some cases that require separating only the fraction in a given string, now this post shares with you how to do this. the following article details how to separate numbers from a given string in excel.