May 2 2007

Left, Right and Mid Formulas in Excel

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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

4 Comments on this post

Trackbacks

  1. Grace said:

    please, i need your help. in a text in excel of 88 x 84 and i need to convert to milimeters, how can i do.
    thank you very much

    January 8th, 2013 at 3:25 pm
  2. Sophia said:

    Example 5 formula does not work. I recreated the data and copied the formula to match the cells but did not work.

    February 11th, 2013 at 3:52 pm
  3. anand said:

    i want need this result is given below .

    sapose that i have a lot of numar but i need some name and number so that how can take by mid formula. condition is given below.

    i need this

    anand kumar chaudhary
    ajay prasad rai
    jai kumar sharma

    i need from first kumar second prasad and third kumar but u don’t without this condition

    mid(a1,6,find(” “,a1) don’t use need other commond.

    April 6th, 2013 at 12:52 am
  4. Methuria said:

    I’m having trouble with this command, but perhaps it has something to do with the way the column that my formula is in – is formatted.

    My data in column A2 is this NREW_MO_201554_CL
    My formula is entered in Cell B2 and is this: =MID(A2,9,6)
    Column B gives me the same result if I format the column as Text or as Number
    my result is the formula rather than the extract =MID(A2,9,6)

    March 10th, 2015 at 2:59 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives