Advanced String Manipulation Using MS Excel - The MID Function in MS Excel
(Page 2 of 4 )
The MID function is not known or popular to any ordinary MS Excel user, yet it is a very powerful function. Its purpose is to extract a block of text within a longer block of text, or in short, it extracts short text within text. Consider the following example.
Say you have the following data in your MS Excel spreadsheet:
1.) What is your name?
2.) Do you know how to use the mid function?
3.) Are you sure you can do this complicated Excel job?
And someone ask you to take out the numbers, so that it will just be:
What is your name?
Do you know how to use the mid function?
Are you sure you can do this complicated Excel job?
You may be laughing at this example, as it can be done manually fast. But note that there are times when you will be asked to process around 100 or even 1,000 rows of data. In that situation, doing it manually can take ages. Thus, extensive knowledge of string manipulation will make your work go more quickly and make you an efficient person when it comes to handling spreadsheet-related jobs.
To solve this problem, copy and paste the above text (with numbers) into an Excel spreadsheet starting on Cell A1, the second question on Cell A2 and the last one on Cell A3.
Now we will define the MID function:
=mid(text, start_num, num_chars)
"Text" is the cell number/location of your text. "Start_num" is where Excel starts picking up characters for your new text. "Num_chars" is the number of characters to be picked up, starting from "Start_num."
To determine the "start_num" of the above example, we will analyze the text for consistencies or patterns. Note that, as instructed, we will be removing the number; for example, "1.)"
So if we count starting with the character "1" until ")" and including the space after it, it comprises four characters.
This means that the desired text starts on every fifth character. This is true for the text in all three cells.
Now we will determine what the "Num chars" are in the example above. Since the length of the characters of the text to be extracted in cell A1 until cell A3 differs a lot, we can set it to "1000." This ensures that the text extracted will not be cut off (especially in longer sentences).
Finally, we are now ready to use the MID function to grab the text we want.
The respective MID formula to extract the text in the illustrated example above:
=MID(A1,5,1000)
=MID(A2,5,1000)
=MID(A3,5,1000)
To use this within the Excel spreadsheet, copy and paste the formula above to cells B1 through B3. This will now grab the text as shown below:
Next: The FIND Function in MS Excel >>
More BrainDump Articles
More By Codex-M
/ 4 
