Separate text from strings in Excel, for example, and how to do it

In Excel, there are 3 basic functions that help you separate text from strings in Excel: LEFT, RIGHT, MID. If you are needing to separate words from the string, you should refer to the article TipsMake.vn shared below.

Separate text from strings in Excel, for example, and how to do it Picture 1

Here's how to use functions to separate text from strings in Excel, for example, and how to make specific, invite you to follow.

LEFT ()

The LEFT function is a function that separates characters from the string on the left.

Syntax : = LEFT (text; n)

Where: text is the string of text you want to separate (the character); n is the number of characters to be separated from the string starting at the first position (if omitting the argument n, the default will be 1).

For example : Separating the first 4 characters in an employee code using the LEFT function.

= LEFT (A3; 4) where A3 is the cell containing the employee code, 4 is the number of characters you want to split.

Separate text from strings in Excel, for example, and how to do it Picture 2

You can learn more about the LEFT function here (http://TipsMake.vn/ham-left-va-leftb-ham-cat-chuoi-trong-excel/).

RIGHT ()

The RIGHT function is a function that separates characters from the string on the right.

Syntax : = RIGHT (text; n)

Where: text is the string to split, n is the number of characters to split from the right of the string.

For example:

Separate 4 numeric characters in employee code as shown below:

Separate text from strings in Excel, for example, and how to do it Picture 3

Use the RIGHT function: = RIGHT (A3; 4) where A3 is the string to split, 4 is the number of characters to split from the right.

Separate text from strings in Excel, for example, and how to do it Picture 4

You can learn more about the RIGHT function here (http://TipsMake.vn/ham-right-va-rightb-trong-excel/).

MID () function

The MID function is a function that separates characters located in the middle of a string.

Syntax : = MID (text; start_num; num_chars)

Inside:

1. text is the string to separate.
2. start_num is the first character position that you need to split in the string.
3. num_chars is the number of characters to split.

For example, you want to split 2 characters starting from the 3rd character.

Separate text from strings in Excel, for example, and how to do it Picture 5

You use the MID function: = MID (A3; 3; 2)

Where A3 is the string of characters and text to split; 3 is the starting position of the split; 2 is the number of characters to split.

Separate text from strings in Excel, for example, and how to do it Picture 6

You can learn more about MID functions here. (http://TipsMake.vn/ham-mid-trong-excel-cach-su-dung-ham-mid-va-en-du-minh-hoa/)

Some examples separate text from strings in other Excel.

Example 1: Separate the account name before the @ sign from the full string.

Assuming you have the following email addresses:

Separate text from strings in Excel, for example, and how to do it Picture 7

To separate the account name before the @ sign you use the LEFT function as follows:

= LEFT (A3; SEARCH ("@"; A3) -1)

Inside:

1. A3 is the full address box you need to separate;
2. SEARCH ("@"; A3) -1 is the number of characters to split, the Search function will help you search for the position of the @ character in the string A3, then subtract 1 from that position to subtract the @ character.

Separate text from strings in Excel, for example, and how to do it Picture 8

Then you copy down to separate other accounts.

Separate text from strings in Excel, for example, and how to do it Picture 9

Example 2: Separate the string after the @ sign in gmail accounts.

Also in the data of example 1, you want to separate the whole string after the @ character you can do as follows:

= RIGHT (A3; LEN (A3) -SEARCH ("@"; A3))

Inside:

1. A3 is the cell containing the data to split.
2. LEN (A3) -SEARCH ("@"; A3) is the number of characters to split from the right, LEN (A3) is the number of characters of the string A3, SEARCH ('@'; A3) is the position of the @ character in sequence. So LEN (A3) -SEARCH ("@"; A3) is the total number of characters of the string to be subtracted minus the number of characters from the beginning of the string to the position of the @ character.

Separate text from strings in Excel, for example, and how to do it Picture 10

Example 3: Separate the letters in two dashes in the string.

Suppose you need to separate the characters between two dashes as shown below.

Separate text from strings in Excel, for example, and how to do it Picture 11

You use the MID function with the SEARCH function as follows:

= MID (C3; SEARCH ("-"; C3) +1; SEARCH ("-"; C3; SEARCH ("-"; C3) +1) -SEARCH ("-"; C3) -1)

Inside:

1. C3 is the string you need to split.
2. SEARCH ("-"; C3) +1 is the starting position to split, the Search function helps you search for the '-' position from the first position, then you need to add 1 to the starting position. The separator will be positioned after the '-' sign.
3. SEARCH ("-"; C3; SEARCH ("-"; C3) +1) -SEARCH ("-"; C3) -1 is the number of characters to split, where the Search function looks for the position of the '- 'second in cell C3 and search from the starting position to split ( SEARCH ("-"; C3) +1 ). After searching for the position of the second '-', it will subtract the position of the first minus sign SEARCH ("-"; C3) and subtract 1 so that when separating will not separate the second '-' This will show the number of characters to split (which is the number of characters in the two minus signs).

Separate text from strings in Excel, for example, and how to do it Picture 12

Then you just need to copy the formula to the cells below and you can split all the strings.

Separate text from strings in Excel, for example, and how to do it Picture 13

Above the article has shared you how to separate text (characters) from text strings in Excel, you need to remember the function of separating characters to apply to each problem, each specific request. Hope this article will help you. Good luck!