Left, Right and Mid Formulas in Excel
- 0 Comments
One of the easiest ways to manipulate a string is by using the left, right and mid formulas. There are many times when users of excel are given a long string and only need a certain section of that string to get the results they desire. Using these formulas will greatly reduce the number of headaches you have when given a string with all the characters together instead of different columns.
Let’s start by taking a look at the formula:
Left(text, Num_Chars)
Right(text, Num_Chars)
Mid(text, Start_Num, Num_Chars)
For the Left and Right Formulas text is the string you want to break apart and num_chars is the number of characters you want the left or right of. For the Mid formula, there is also a start_num which tells the formula where in the string you would like to start taking the number of characters from.
[ad#in-post-ad]
Examples
For the examples below using the table above, we want to break apart the strings in A2 and A3 so that the city, state and zip are in separate columns.
Example 1: =left(A2,8) will return “Columbus”
Example 2: =mid(A2,11,4) will return “Ohio”
Example 3: =right(A2,5) will return “43228″
There are a couple tricks you can use if the length of the string you are looking for varies in each case or row. For example, Denver is only 6 characters and Columbus is 8 Characters. So for this next example we will tell the formula to look until it finds a comma the return everything before that.
Example 4: =LEFT(A3,FIND(“,”,A3)-1) will return “Denver”
Example 5: =LEFT(RIGHT(A3,LEN(A3)-FIND(“,”,A3)-1),FIND(” “,RIGHT(A3,LEN(A3)-FIND(“,”,A3)-1))-1) will return “Colorado”
In the last two examples I used the find formula to break down where in string we wanted to formula to work for any examples with “city, state zip”. I will soon add a tip on how to use all the features of the find formula and what is useful for. Hopefully that will make your next project that much easier and smoother in the future.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.