The function takes a string in Excel

The function takes a string in Excel Picture 1

The function to get the string in Excel includes 3 types of LEFT, MID and RIGHT with different string functions. To better understand the three types of functions that take these strings, please follow the following article of TipsMake.com.

1. The formula of the function takes a string of characters

Basically, the string grab function has the function of retrieving a consecutive string from another long string. Speaking of which, you certainly have a glimpse of the other three functions that take strings based on their names.

The names of these 3 functions are in English letters, of which LEFT is left, MID stands for middle meaning middle and RIGHT is right.

From there you will see the effect and how to get the sequence of these functions, it depends on the position of the character you need to get from the original character.

With the LEFT function , you get the character string from the left of the original string.

MID function , you will get the string between.

And the RIGHT function , you get the text string to the right of the original.

1.1. The function takes the string LEFT

The formula of the function takes the LEFT string :

= LEFT (String of original characters; number of characters to be retrieved)

Inside:

The original character string is the original string for you to extract and retrieve the consecutive characters you need.

The number of characters you will retrieve is the number of characters you will retrieve from left to right.

As the example below, we need to get the first letter in the series of original characters so the required formula is:

= LEFT (A2; 1)

The function takes a string in Excel Picture 2

Then you copy the formula down by double clicking on the bottom right corner of the formula cell or clicking on that position and dragging down.

The function takes a string in Excel Picture 3

1.2. The function takes the MID string

Formula of the MID function:

= MID (Original string; starting position of the string, number of characters removed)

Inside:

The original character string is the original string for you to extract and retrieve the consecutive characters you need.

The starting position for the string is written in numbers. Unlike the LEFT function , the MID function starts to retrieve a string from any position in the middle of the original string. That's why we need to tell this function where we need to start to get characters from. The number will count the number of characters from the first character to the last character.

The number of characters you will retrieve is the number of characters you will retrieve from left to right.

As the following example, we need to get the middle character from position 2, and the number of characters to get is 3 so we have the formula:

= MID (A2; 2; 3)

The function takes a string in Excel Picture 4

Then you copy the formula down by double clicking on the bottom right corner of the formula cell or clicking on that position and dragging down.

The function takes a string in Excel Picture 5

1.3. The function takes the string RIGHT

The formula of the function takes the string RIGHT:

= RIGHT (The original string of characters; the number of characters to be retrieved)

Inside:

The original character string is the original string for you to extract and retrieve the consecutive characters you need.

The number of characters you will retrieve is the number of characters you will retrieve from right to left.

As the example below, we need to get the first letter in the series of original characters so the required formula is:

= RIGHT (A2; 3)

The function takes a string in Excel Picture 6

Then you copy the formula down by double clicking on the bottom right corner of the formula cell or clicking on that position and dragging down.

The function takes a string in Excel Picture 7

2. Character string function combined with other functions

A simple example of practical functions that take LEFT , MID and RIGHT characters often goes in conjunction with conditional functions such as the IF function or the VLOOKUP reference lookup function .

Based on the example used above, I can use the IF function to specify the selling method of orders.

We have the following formula:

= IF (LEFT (A2; 1) = "S"; "Ship"; "Cost")

Explain:

If the first letters of the Order ID are S , the value "Ship" is returned, otherwise the "Cost" value is returned. So we can see we need a string grab function here to extract the first letter in the Order ID string .

The function takes a string in Excel Picture 8

Then you just copy the formula used to all the lines below.

The function takes a string in Excel Picture 9

But the IF function can only be used when you have two different types of results (Ship and Cost) but when there are too many types of results to collate, you must use the RIGHT function .

We have the formula combined with the VLOOKUP function as follows:

= VLOOKUP (RIGHT (A2; 3); $ A $ 11: $ B $ 18; 2; 0)

Explain:

We rely on the last 3 letters of the Order ID with the code comparison table below to know the full name of the item. So the same principle as using IF , we need RIGHT to retrieve the last three characters of the Order ID so that VLOOKUP can read and understand what the item code needs to be compared.

The function takes a string in Excel Picture 10

Finally, you copy the formula down to get a list of item names.

The function takes a string in Excel Picture 11

Thank you for following up on our article on the string grab function in Excel. TipsMake.com hopes that this article will give you a little more knowledge about the types of Excel string functions.