Buzz
Excel provides three fundamental functions to extract letters from strings: LEFT, RIGHT, and MID. If you need to separate letters from a string, refer to the article shared by Mytour.
Explore the specific methods and step-by-step instructions for extracting letters from strings in Excel. Follow along to learn how to use these functions effectively.
LEFT Function()
The LEFT function is used to extract characters from the left side of a string.
Syntax: =LEFT(text; n)
Where: text is the character string you want to extract characters from; n is the number of characters to be extracted from the string starting from the first position (if you omit the n parameter, it defaults to 1).
Example: Extract the first 4 characters from the employee code using the LEFT function.
=LEFT(A3;4) where A3 is the cell containing the employee code, and 4 is the number of characters you want to extract.
Learn more about the LEFT function [here](https://Mytour/ham-left-va-leftb-ham-cat-chuoi-trong-excel/).
RIGHT Function()
The RIGHT function is used to extract characters from the right side of a string.
Syntax: =RIGHT(text; n)
Where: text is the character string to be extracted, and n is the number of characters to be extracted from the right side of the string.
Example:
Extract 4 numeric characters from the employee code as shown below:
Using the RIGHT function: =RIGHT(A3;4)
Learn more about the RIGHT function [here](https://Mytour/ham-right-va-rightb-trong-excel/).
MID Function()
The MID function is used to extract characters from the middle of a string.
Syntax: =MID(text; start_num; num_chars)
Where:
- text is the character string to be extracted.
- start_num is the position of the first character to be extracted in the string.
- num_chars is the number of characters to be extracted.
Example: If you want to extract 2 characters starting from the 3rd character.
Use the MID function: =MID(A3;3;2)
Where A3 is the character string to be extracted; 3 is the starting position for extraction; 2 is the number of characters to be extracted.
Learn more about the MID function [here](https://Mytour/ham-mid-trong-excel-cach-su-dung-ham-mid-va-vi-du-minh-hoa/).
Some other examples of extracting text from strings in Excel.
Example 1: Extract the account name before the @ sign from the full string.
Suppose you have email addresses as shown in the table below:
To extract the account name before the @ sign, you can use the LEFT function like this:
=LEFT(A3;SEARCH('@';A3)-1)
Where:
- A3 is the full address cell you want to extract from;
- SEARCH('@';A3)-1 is the number of characters to be extracted. The Search function helps locate the position of the @ character in the A3 string, then subtracts 1 to exclude the @ character.
Then, copy it down to extract other accounts.
Example 2: Extract the string after the @ sign in Gmail accounts.
In the data of example 1, if you want to extract the entire string after the @ character, you can do the following:
=RIGHT(A3;LEN(A3)-SEARCH('@';A3))
Where:
- A3 is the cell containing the data to be extracted;
- LEN(A3)-SEARCH('@';A3) is the number of characters to be extracted from the right. LEN(A3) is the total number of characters in the A3 string, and SEARCH('@';A3) is the position of the @ character in the string. Therefore, LEN(A3)-SEARCH('@';A3) is the total number of characters in the string to be extracted minus the number of characters from the beginning of the string to the position of the @ character.
Example 3: Extracting characters between two hyphens in a string.
Suppose you need to extract characters between two hyphens as shown below.
Use the MID function combined with the SEARCH function like this:
=MID(C3;SEARCH('-';C3)+1;SEARCH('-';C3;SEARCH('-';C3)+1)-SEARCH('-';C3)-1)
In this context:
- C3 là chuỗi ký tự các bạn cần tách.
- SEARCH("-";C3)+1 là vị trí bắt đầu cần tách, hàm Search giúp các bạn tìm kiếm vị trí dấu “-“ từ vị trí đầu tiên, sau đó các bạn cần cộng thêm 1 để vị trí bắt đầu cần tách sẽ là vị trí sau dấu “-“.
- SEARCH("-";C3;SEARCH("-";C3)+1)-SEARCH("-";C3)-1 là số lượng ký tự cần tách, trong đó hàm Search tìm kiếm vị trí của dấu “-“ thứ 2 trong ô C3 và tìm kiếm từ vị trí bắt đầu cần tách (SEARCH("-";C3)+1). Sau khi tìm kiếm được vị trí của dấu “-“ thứ 2 thì sẽ trừ đi vị trí của dấu trừ thứ nhất SEARCH("-";C3) và trừ đi 1 để khi tách sẽ không tách cả dấu “-“ thứ 2 như vậy sẽ ra số lượng ký tự cần tách (chính là số lượng các ký tự trong hai dấu trừ).
After that, you only need to copy the formula down to the cells below, and you can extract all the string characters.
This article has shared with you the method of extracting letters (characters) from text strings in Excel. Remember the character extraction functions to apply to specific problems and requirements. Hope this article helps you. Wish you success!
