Excel offers a variety of different formulas and functions for everyday use. This week, we will focus on the MID function. This function allows you to isolate a sub-string of data within a larger string in a new cell. The syntax is simple and straightforward, making it an ideal function for quick tasks.
There are many reasons and ways you will use the MID function. Today’s example comes directly from a Learn Excel Now fan who emailed us with an issue he was having.
“The most boring and time-consuming task I have at work is to pull the processing numbers out of the sales database to send to our warehouse team. The sales report exports into Excel and all of the order info is jammed together on one line. I have to go line by line and rewrite the individual processing number. Depending on our daily orders, it can take me several hours to finish. And with so many lines to go through, it’s really easy to miss a number or get the line wrong. My boss has talked to me about it. Can you help?”
Yes, Brian, we are here to help. Let’s explore how the MID function can be used to help your task.
The syntax for the MID function is:
=MID(Text,Start_number,Number of Characters)
Simply put, this is saying: pull the data out of this range (text), starting on character (Start_Number) going this many characters after it (Number of Characters).
This spreadsheet will be used to approximate Brian’s report:
As you can see, the Processing Number starts on character 13 and is 5 characters long. Thus, the way we would write this formula is:
Now the processing number is nice and clear on its own line:
IMPORTANT: In this example, each line has same the number of characters. So, you can copy and paste all the way down, however, for lines with different numbers of characters, you will need to count to the starting and end points individually.
For Brian, using the MID function will save a ton of time and ensure accuracy.
We at Learn Excel Now hope you found this week’s tip on the MID function useful. There are many times this will come in handy, you just have to recognize the opportunity!
Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.
Kevin – Learn Excel Now
Great tip, didn’t know about this formula before!