Left Right Middle Excel functions

Left, Right, Middle Excel functions

In this segment you will learn how to use the left right middle excel functions.

In this example we have a list of Account numbers and what we’d like to do is split them up, because if you look at the account number, you’ll see that the first three letters appear to indicate the City, the next five numbers is a Client number of sorts, and the last three numbers is the Department. And we’d like to split it up so that perhaps you can compare it to other reports which are not necessarily in this format.

In order to do this, we can make use of the LEFT, RIGHT and MID functions in Excel. So for example City, the city code seems to be on the left hand side of the text, so we’re going to make use of the LEFT function,

• so we click on the Function Wizard
• and find LEFT,
• we say ok,
• and what you’re asked is, it asks you for the reference to the cell that contains the text. So that would be there
• and then is says tell me how many characters from the LEFT you want to be extracted, and in this case you can see it appears to be three
• and if you type in a 3
• and say ok,
• only that part of the cell will be extracted.

For the department number you can see that it appears to be from the RIGHT, and again its three characters,

• so we can click in this cell
• activate the Function Wizard
• find the RIGHT function
• click ok,
• again it asks for a reference to the text, we click on that cell
• and it asks for the number of characters from the RIGHT that it must extract and as you can see it is three as we don’t want the dashes
• so we say 3
• and when we click ok
• you’ll see that you’ve now extracted just the last three characters from the text.

To extract characters from the middle of a section of text you need to use the MID function,

• so you click on the cell
• activate the Function Wizard
• and find the MID function,
• click ok
• again you are asked for a reference to the cell that contains the text, so its over here,
• this criteria says what is the position of the first character you want to extract, and in this case you’ll see we don’t want those three, and we don’t want the dash, so it’s the fifth character
• so we put a 5 in here
• and the number of characters is how many you want to extract from that fifth position and in that case it is 1,2,3,4,5, we put in 5 here,
• and when we click ok
• you’ll see it extracts only the numbers in the MIDDLE of the text

You are now in a position to

• highlight these three
• and copy it all the way down
• and automatically you have split these codes into their components being
• the City
• Client number
• And Department

and you are now able to perhaps sort for more reliable information.