Buzz
In Excel, to concatenate strings together, we use the Concatenate function. Similarly, when wanting to split into multiple strings, we also have string cutting functions: Right, Left, and Mid. How to use these functions? Let's follow along through the guide below.
CONCATENATE FUNCTION IN EXCEL
The Concatenate function is used to join strings together. For example, if you enter First Name and Last Name in separate cells but later need to merge them into one cell, to use the Concatenate function, we'll have the syntax as follows:
Concatenate(text1, text2, ...)
Where:
Text1: is the first string. Required.
Text 2 ...: optional. Can be up to a maximum of 255 strings. Strings must be separated by commas.
For example, we have the following data table:
STRING CUTTING FUNCTIONS IN EXCEL
Here we have 3 string cutting functions including:
- The Right function: Used to cut and retrieve the right part of a string
- The Left function: Used to cut and retrieve the left part of a string
- The Mid function: Used to cut and retrieve the middle part of a string
How to use the Right function:
- Syntax: RIGHT(text, n)
- Where: + Text: string of characters.
+ n: Number of characters to cut from the string of characters. (If this parameter is not specified, Excel will default to a value of 1).
- Functionality: Cuts out n characters in the text string starting from the right side.
Example: String cutting without the parameter n:
Applying the RIGHT function to extract characters from the 'Student ID' column without the parameter n.
- In cell G5, enter the formula as follows: E6= RIGHT(D6) and press Enter.
- Cell D6 contains the data you want to cut the string from.
- The result will be displayed in cell E5
How to use the Left function:
Syntax: = LEFT(text, n)
Where: - text: String of characters.
- n: Number of characters to cut from the string of characters. (If this parameter is not specified, Excel will default to a value of 1).
Functionality: Cuts out n characters in the text string starting from the left side of the string.
Example:
To cut a character from the left side of the string, you use the LEFT function.
Here to retrieve the first character of the employee ID with the formula: E5=LEFT(C5)
How to use the MID function:
- Syntax: MID(text,m,n)
- Where: + Text: String of characters.
+ m: Starting position for cutting the string of characters.
+ n: Number of characters to cut from the string of characters.
- Functionality: Cuts out n characters in the text string starting from position m.
Example:
Based on the Student List table, filter out the province code of the student with two characters in the middle. T
- Enter the formula in cell E5 as follows: E5= MID(D6,3,2) - Returns 2 characters of string D6 starting from the 3rd character
- Drag down the remaining cells to copy the formula.
We obtain the following result:
Above is the compilation of string cutting and concatenation functions in Excel, through these operations, it is hoped that you will understand the functions and how to use them in specific cases.
In addition, Excel has many important functions in the calculation and usage process, Vlookup function is a specific example. Users often use the Vlookup function to find and filter out important data according to predefined conditions in a spreadsheet with a lot of data. Readers can refer to how to use the Vlookup function in Excel to master this function better.
