Advanced String Manipulation Using MS Excel

Let's make a realistic scenario. Say your boss has assigned you to do some job that requires your skills in Microsoft Excel. You then open your Excel spreadsheet and paste in your data. BANG! You do not know the functions that will give you your desired output. You search the Excel Help, and then become confused even further.

Your deadline is approaching; you spend a lot of time finding those answers. You have no choice but to manually perform the strenuous spreadsheet manipulation without the use of those functions. You hit the deadline with the work unfinished; you failed and get a low job rating from your employer.

Its time to learn some Excel functions before this type of situation happens to you.

String manipulation in Excel is highly useful in an office environment, particularly if you're working in the engineering, information technology and business sectors. MS Excel offers several built-in functions to enable you to make your spreadsheet work easier and more efficient.

In this tutorial, we will cover the three important string manipulation functions considered to be advanced in MS Excel skill sets. These are:

•  The MID function
  
• The FIND function
  
• The LEN function

The MID Function in MS Excel

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:

The FIND Function in MS Excel

The FIND function in MS Excel makes your work even more efficient, especially when combined with the MID function.

Here is the definition of the FIND function in MS Excel:

=FIND(find_text,within_text,[start_num])

The "find text" is the character for which you are looking in the text. It can be only one character or even a block of text. You have to enclose the characters to be searched in between double quotes (").

The "within_text" part refers to the cell location or address of the text to be analyzed, while "start_num" tells Excel where to start searching (is it in the first character or in the succeeding characters?).

Here are some illustrative examples. One of the most difficult tasks of any webmaster is to extract the official URL out of the session ID-based URLs. Imagine that you are about to make a sitemap with the following URLs:

http://www.yoursessionidbasedwebsite.com/file-x-y-1.html?osCsid=g25145xf

http://www.yoursessionidbasedwebsite.com/file-x-y-2.html?osCsid=g25145xf

http://www.yoursessionidbasedwebsite.com/file-x-y-3.html?osCsid=g25145xf

http://www.yoursessionidbasedwebsite.com/file-x-y-4.html?osCsid=g25145xf

http://www.yoursessionidbasedwebsite.com/file-x-y-5.html?osCsid=g25145xf

http://www.yoursessionidbasedwebsite.com/file-x-y-6.html?osCsid=g25145xf

The above example is simple because it is only used for illustration purposes; in real world filtering of session IDs, things can get pretty complicated, especially if you have other long and ugly characters in the URLs (aside from the session ID), such in today's modern dynamic websites.

To take out the session ID above, you can analyze for similarities, just as we did in the MID function example previously. Note that all URLs have session IDs beginning with ?oSCsid, but we cannot filter by "?" since there could be other URLs with two "?" characters (in a real world session ID-based website) . Therefore we will want to find the location of osCid, and then use that location as the start of where things need to be filtered. For example, in this URL: http://www.yoursessionidbasedwebsite.com/file-x-y-4.php?osCsid=g25145xf , the location of "osCsid" is in the fifty-seventh character. So we will start filtering on the (57-2) = 55th character to include the "?" before the osCsid which is a part of the session ID. The result no longer contains the session ID.

To implement the above example in Excel, copy and paste the six URLs above to an Excel worksheet starting in cell A1; the last one will occupy on cell A6. Remove the spaces in between.

In cells B1 through B6, copy and paste the formula below:

=FIND("?osCsid",A1,1)

=FIND("?osCsid",A2,1)

=FIND("?osCsid",A3,1)

=FIND("?osCsid",A4,1)

=FIND("?osCsid",A5,1)

=FIND("?osCsid",A6,1)

The above formula means we will start searching the first character, looking for "?osCsid".

Finally, in cells C1 through C6, copy and paste the MID formula below, which will then extract the clean URL (without the session ID) in cells A1 through A6.

=MID(A1,1,B1-2)

=MID(A2,1,B2-2)

=MID(A3,1,B3-2)

=MID(A4,1,B4-2)

=MID(A5,1,B5-2)

=MID(A6,1,B6-2)

After this text manipulation, you should have the same results as shown in the screen shot below:

The LEN function in MS Excel

The LEN function is probably the simplest of all text-related functions. Its job is to simply count the number of characters inside the cell, even SPACES! So be careful; do not rely on the LEN function to count only characters. I highly recommend using the LEN function mixed with other string-cleaning functions. This is to ensure that your string manipulations are correct.

For example if you have something like:

Beautiful women

placed in Cell A1, it comprises 15 characters. But if there are spaces before or after any words, your result with LEN will be more than 15 characters.

To clean the text to be analyzed, it is recommended to use this set of functions:

=trim(clean(A1))

The job of this function is to remove any spaces as well as non-printable characters. Finally the LEN function (including the cleaning formulas) will be:

=LEN(trim(clean(A1)))

To use this one in the actual Excel spreadsheet, we will use our first example in this article:

Instead of using:

=MID(A1,5,1000)

=MID(A2,5,1000)

=MID(A3,5,1000)

You can have:

=MID(A1,5,LEN(TRIM(CLEAN(A1))))

=MID(A2,5,LEN(TRIM(CLEAN(A2))))

=MID(A3,5,LEN(TRIM(CLEAN(A3))))

This can accurately count the number of characters, instead of using 1000 as the default value, and still produce the same results. Note that the LEN function is very versatile; it can be used along with FIND, MID and other text-related functions, as shown in the examples in this article.

Summary

We have successfully illustrated how to use the MID, FIND and LEN functions. These are very important Excel functions for conducting advanced text manipulations. You can start varying the example above and incorporate it into your daily work. As soon as you start acquiring these skills, they will help you to tremendously improve your office and analytical productivity using MS Excel.