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 .
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 on 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 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.
- When you press Enter automatically capitalizes and removes extra spaces.
Good luck!
You should read it
- How to split strings in Excel
- How to use MID functions to get strings in Excel
- The LEFT function, how to use the left-hand string cutting function in Excel
- The function takes a string in Excel
- How to change the direction of the Enter key in Excel
- String (String) in C #
- How to use ConcateNate function on Excel
- String (String) in PHP
May be interested
- Instructions to merge multiple Excel files into 1 Filedetailed instructions on how to combine multiple excel files into a single file with macro.
- Count the number of Saturdays and Sundays in any period in Excelinstructions to count the number of saturdays and sundays in any period in excel. the following article details how to calculate the number of 7th, sunday or any day in excel in many ways.
- Summary of data from multiple Sheets in Excelthe following article details how to aggregate data from multiple sheets in excel.
- Split numbers from strings in Excelin 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.
- Calculate by cell background color in Exceldetailed instructions on how to sum data cells by cell background color in excel by building a macro function that calculates the cell background color. example: the colors have corresponding values. sum the values corresponding to the cell's background color.
- Instructions for using PivotTable in Excel - How to use PivotTablea pivottable is a tool used to analyze data from different angles and requirements from a list or a table. from the initial huge data block, the pivottable can help you to aggregate data in groups and collapse data as required.