HomeApplicationOffice information

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 .

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

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 .

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

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 .

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

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".

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

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".

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

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).

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

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.

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

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

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

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.

5 ★ | 1 Vote

You should read it

May be interested

  • Exponential functions in Excel - Usage and examplesExponential functions in Excel - Usage and examples
    exponential functions in excel - usage and examples. are you looking for exponential functions in excel to use instead of the exponential operator '^'? so let's learn the exponential function in excel that the article shares below.
  • Instructions for cutting strings, concatenating strings in ExcelInstructions for cutting strings, concatenating strings in Excel
    you are looking for a way to cut strings, concatenate strings in excel to separate names or concatenate text strings. so please refer to the following article for instructions on how to cut strings, concatenate strings in excel.
  • The function takes whole parts in Excel - Specific examplesThe function takes whole parts in Excel - Specific examples
    the function takes whole parts in excel - specific examples. excel has 2 functions that involve taking integer parts: quotient, int. there are many people who have a confusion between these two functions, only when you understand the nature of each one can you use them most accurately.
  • How to split strings in ExcelHow to split strings in Excel
    in excel when working with character strings, there are many cases where it is necessary to split a string to use the data, but the amount of data needs to be separated quite large, if merely manually doing it takes a lot of time and effort. strength. the following article guides to
  • LEFT and LEFTB functions - Functions for cutting strings in ExcelLEFT and LEFTB functions - Functions for cutting strings in Excel
    left () and leftb () are two string-cutting functions that are used a lot in string processing with excel spreadsheets. the left () and leftb () functions help you cut the string to the left of the text string as quickly and efficiently as possible.
  • Basic functions in Excel, calculation formulas and illustrative examplesBasic functions in Excel, calculation formulas and illustrative examples
    if you are new to spreadsheets, basic functions in excel such as sum, min, max, if will help you solve your work quickly and effectively. let's learn with free download now.
  • CONCATENATE function: Concatenate cell contents in Excel into new textCONCATENATE function: Concatenate cell contents in Excel into new text
    the concatenate function in excel is a function used to concatenate text. below are details on how to use the concatenate function in microsoft excel.
  • COUNT function in SQL ServerCOUNT function in SQL Server
    this article will show you in detail how to use functions that handle count () numbers in sql server with specific syntax and examples to better visualize and capture functions.
  • Date time functions in ExcelDate time functions in Excel
    excel supports you to process and calculate quickly with the functions that excel provides such as calculation functions, date functions ... one of them is the function of time to help you handle the prices. time value: hour, minute, second conveniently
  • Common calculation functions in Excel, formulas and examplesCommon calculation functions in Excel, formulas and examples
    common calculation functions in excel not only help you process data quickly but also increase the accuracy of calculations. let's learn about the sum, average, max, min functions with free download to improve work efficiency.

Office information