Summary of functions that handle character strings in Excel, syntax and examples

Excel is extremely powerful office software with features that help you process data, calculate, make statistics,. This is especially important software for accountants. To know how to extract data in Excel, especially large amounts of text, it is impossible not to know the following summary of functions to handle character strings in Excel. Let's learn how to use these functions with TipsMake.

1. LEFT() function

The Left() function in Excel helps us separate characters counted from left to right of a string of characters or a cell containing that character. Below is the Left function syntax and an example

Syntax: LEFT(Text, Number of characters)

- Text: you can enter the character string or code of the cell containing the character string to be extracted.

- Number of characters: number of characters from left to right to be extracted

For example: As shown in the image below, to extract the first 5 characters of the string "TAIVN20146SGN" corresponding to column D8, we have =LEFT(D8,5) in the formula cell in Excel. The first 5 characters separated as "TAIVN" are entered in the LEFT Function column .

2. RIGHT() function

Similar to the Left() function but has the opposite character extraction direction, from right to left of the character string or cell containing characters.

Syntax: RIGHT(Text, Number of characters)

- Text: you can enter the character string or code of the cell containing the character string to be extracted.

- Number of characters: number of characters from right to left to be extracted

For example: As shown in the image below, to extract the first 4 characters of the string "TAIVN20146SGN" corresponding to column D8, we have =RIGHT(D8,4) in the formula cell in Excel. The first 4 characters separated as "6SGN" are entered in the RIGHT Function column .

3. MID() function

The MID function is a function that retrieves the specified number of characters in a character string. If the character string does not have enough characters to meet the requirements of the MID function, the entire string will be taken.

Syntax: =MID(Text, Left, Number of characters)

- Text: you can enter the character string or code of the cell containing the character string to be extracted.

- Left: start taking the next character from the left.

- Number of characters: number of characters to be extracted

For example: As shown in the image below, to remove the 4th character from the left, there are 3 characters of the string "TAIVN20146SGN" located on the cell containing D8, we have the syntax =MID(D8,4,3), we get the string of characters. the word "VN2" in the MID Function box .

4. LOWER() function

To convert all characters in a cell containing a string of characters into lowercase, we have the Lower function.

Syntax: = LOWER(Containing cell)

- Containing cell: enter the containing cell code or character string that you want to change to lowercase characters.

For example: To change the entire uppercase string "TAIVN20146SGN" located in the cell containing D8 to lowercase letters, we have the syntax =LOWER(D8), we get its lowercase character string "taivn20146sgn".

5. UPPER() function

In contrast to the Lower function, we also have the Upper function to turn lowercase characters and letters into uppercase characters. Below is the Upper function syntax and an illustrative example

Syntax : =UPPER(Containing cell)

- Containing cell: enter the containing cell code or character string that you want to change to uppercase characters.

For example: To change the entire lowercase string "taivn20146sgn" located in the cell containing D8 into uppercase letters, we have the syntax =UPPER(D8), we get the uppercase string "TAIVN20146SGN".

6. PROPER() function

The PROPER function supports converting the first character in each word to uppercase and the remaining characters in the word to lowercase. Refer to the PROPER function syntax and illustrative examples

Syntax: =PROPER(Containing cell)

- Containing cell: enter the containing cell code or character string you want to convert

For example: Convert all characters "taivn20146sgn" in column D8 to "Taivn20146sgn", we type the syntax =PROPER(D8).

7. LEN() function

Count the number of characters in the string including letters, numbers and special characters.

Syntax: =LEN(Containing cell)
- Containing cell: enter the containing cell containing the character string to count.

For example: To count the number of characters in the string "taivn20146sgn*" in cell D8, we have the syntax =LEN(D8) in the formula cell, the number of characters 14 will be returned in the LEN function cell.

8. TRIM() function

Removes blank characters that exist at both ends of the string.

Syntax: = TRIM(Containing cell)

- Cell containing: enter into the cell containing a string of characters with characters that appear at both ends of the string

For example: In the cell containing D8, there is the string "taivn20146sgn*" with blank characters at both ends of the string. To remove this blank character, we have the syntax =TRIM(D8) to get "taivn20146sgn*" in the TRIM Function column. in an Excel spreadsheet

Above is a summary of functions that process character strings in Excel, syntax and examples to help you grasp the uses of the functions and how to use them. But this function will bring high efficiency when you need to process large data series and solve the job quickly.

In addition, before learning more in-depth functions of Excel, you should also refer to the basic functions in Excel to know how to use basic functions such as MAX, MIN. as well as equipped with other tools. Necessary tools when using this office software.

Lesley Montoya

Update 18 June 2024