Separating Cell Information in Excel with Left, Right, and Mid Functions
The Separation Functions
There are three actual functions in Excel that separate data in a cell. All three require you to tell it what the cell being separated is, where the start point is, and how many characters you want to separate. Depending on which one you’ve selected you will get the first few characters, the last few characters, or a specified number of characters from the midsection.
With the Data Tools you needed something that was exactly the same in all the cells you were going to separate, In the functions, you just need to know how many characters you want returned. The functions are much more powerful but they are also much more complicated and complicated translates to a higher potential for error.
For this example we will be using our standard four name spreadsheet with the ‘Email’ section removed for space purposes. For the LEFT and MID functions we will be using the ‘Home Phone’ column and for the RIGHT function we will be using the ‘City’ column.
The LEFT Function
The syntax for the LEFT function is LEFT(cell, number of characters). That means beginning at the leftmost position of the cell, return a certain number of characters. So if you had the telephone number 123-456-0789 in cell ‘A2’ and you wanted the area code separated the function call would read ‘LEFT(A2,3) and the return would be 123.
First select a cell for the function (1). Remember this is where your information will be returned so make sure it’s where you want it. Next go to the ‘Formulas’ tab (2), then over to ‘Insert Function’ (3) on the extreme left.
The Insert Function Dialog Box
This opens the ‘Insert Function’ dialog box. This box has three areas, the search area, the category area, and the functions area. Since we know what we are looking for we can just go to the search box and type the word LEFT (1) into it and click on ‘Go’ (2). Notice that the LEFT function is now in the topmost position on the list and is highlight in blue. Just click ‘OK’ (3) to select it.
The Function Arguments Dialog Box
The Function Arguments dialog box is now open. Notice that you are required to supply two pieces of information. The first is the location of the text that is to be separated and the second is the number of characters you want returned. We want to return the area code for all the telephone numbers so we want the first three numbers.
There are a couple of things to take note of here. First, look at the Function Arguments box and notice that the column designation for the ‘Home Phone’ column is in the ‘Text’ box. Over to right of the box the information from that cell is displayed. In the second box is the number of characters you want returned, in our case three. There are two places in this box where the results you will get is displayed. In the upper right under the phone number and the number of characters to return and in the lower left next to the ‘Formula result’ statement.
Second, notice in the address bar and in the cell that the LEFT formula is there in the format we went over in The LEFT Function capsule above.
If you’re satisfied with the result you’re going to get, click ‘OK’.
Now we have the area code in column ‘G’. Notice again that when we used Data Tools we actually removed the data from the cell and placed it in another cell. With the functions the original data stays the same and the characters you want are displayed in the new column.
To get all the area codes, simply place the cursor in the ‘G2’ cell, grab the little plus sign in the lower right corner of the cell, hold it down with the left mouse button, and drag it down cover all the names on your list. Format the new column and give it a heading and you’re done.
But Not Really
If you look at the spreadsheet you see the numbers that are the area codes. If you print the spreadsheet you will see the numbers that are the area codes. But if you place your cursor in one of the cells with the area code you will only see the formula for retrieving the numbers used in the area code and, if for some reason, you delete the telephone number, the new cell will display an error.
Remember: When you use a function you only get a reference back to the original cell.
There is a solution to this problem (1, 2). Select the data in the column (3), copy it (4), and paste it back as a value (5). The area code is now a number and not a reference (6, 7).
The RIGHT Function
The RIGHT function is the same as the LEFT function except for beginning its count from the right side of the cell instead of the left. The syntax is the same, the result is the same, and the problem with a reference rather than a value is the same. For this example we are going to use the ‘City’ field and pull the zip code out of the address and place it in its own column we name ‘Zip Code’. This video will show you how to do the RIGHT function.
The MID Function
The MID function takes data from the middle of a cell and places it in a new column. This function is more versatile than either the LEFT or RIGHT function but it also has a third variable which makes it more prone to error. For this example we will go back to the ‘Home Phone’ and pull the middle 3 numbers and place them in a new column we call ‘Exchange’.
Since we know how to get to the ‘Function Arguments’ dialog box, we will start this instruction there. This dialog box requires 3 inputs. The first is the same as in the LEFT and RIGHT function, the cell that has the data to separate. The ‘Start_num’ box is asking you for the number location of where you want to start pulling data. For this exercise we are taking the middle 3 numbers of the phone number so if the phone number is 703-555-1234 then what we want is 555 and we want the count to begin at the 5th character. It is only a coincidence that the number we want is also a 5.
Insert the formula for all the addresses, name the column, and format the cells. Remember, the information in the cells is still just a reference so you need to copy them and paste them back as values to get real values in the cells.
That’s all there is to these three functions. They can be very useful to you in some situations but there are some important points to remember when using the functions. The functions only return a reference point, if you want a value, you MUST copy the information and paste it as a value. The other potential problem of the function is that it does not remove the data you are pulling from the original cell so if you want it removed you will have to manually remove it. Because everything that is done in a function is relative to where it comes from you must also remember to make your new column values before removing the data from the original cell. Expect to make mistakes occasionally and when they happen remember the most important key in spreadsheet manipulation—undo. Good luck and enjoy working with these functions.
More by this Author
When you have to use the same information in more than one location you need a way to update that information everywhere it's used. Paste -> Link is the answer and I'll show you how...
Mail merge is one of the most useful processes to be found in Word. It can be used on everything from a standard form letter to your annual Christmas letter. This hub will make you a mail merge expert
No comments yet.