masthead for advanced excel

Mid Function

MID Function is used to extract characters, words, phrases and even numbers from a cell. Mid refers to middle. It means that it can easily extract the middle part of the word or phrase. Assuming there is a cell with the text "71495865 What is Excel?" and you want to extract the middle part of the text "What is Excel". You can do so using the MID function. Here is how.

  1. Suppose the phrase "71495865 What is Excel?" is in cell A1 and you do want to show the phrase "What is Excel?"
  2. And in cell B1, entered the mid formula as follows "=MID(A1,10,255)?.
  3. The cell will return the result "What is Excel?"

Mid Function explained

  1. In the above example, A1 is the cell that contains the phrase we wish to extract. It is the first piece of information that we need to put into the formula.
  2. After the comma following the cell A1, we indicate the position which we want Excel to start extracting the characters. The position include spaces if there is any. "=MID(A1,10,255)"
  3. And finally, we gave instruction on the number of characters we want Excel to extract from the starting position indicated in step b. We have put in the number 255, a relatively large number so that we could extract all the chatacters after the position 10. "=MID(A1,10,255)"

The RIGHT function

The RIGHT Function works in a similar way as the MID function. The difference, the RIGHT function picks up text from the right to the left. It is useful if you are trying to extract number at the end of the text in a cell. One example is the postal code which consist of a certain number of digits. Unlike the MID function, the RIGHT formula only has 2 parts, because it always starts from the right so there is no need to have the starting position. For example, if the postal code in A1 is the last 6 digits, you just have to enter the formula as (without quotes) "=RIGHT(A1,6)".

The LEFT function

The LEFT function is the direct opposite of the RIGHT function. It extracts text or number from the left to the right. This function can be easily replace by the MID function. Just enter the number 1 in the starting position and you would have replicated the LEFT function using the MID function.

back to the top of MID Function


New! Comments

Have your say about what you just read! Leave me a comment in the box below.