Working With Text in Excel

Some projects require that you download data from other programs, sometimes in long text strings. Excel has functions that allow you to easily isolate different parts of the string and organize the data into columns. Using the techniques in this article, you can convert thousands of records in just a few minutes.

The Left, MID, and SEARCH functions are three of the most useful formulas in Excel. We will explore these formulas in depth and show you how to use them to pinpoint specific text in long strings and organize the data into neat columns and rows. Then you can simply append them to your Access tables or use them in your own Excel applications. So, let’s get started.

Suppose you downloaded thousands of records from a mainframe database that had the following format: PA4251234567-Acme Publishing Company

Where: the first character was a record type, the next four represented the budget center, the next seven characters a contract number, and finally anything after the dash represents the customer’s name.

How would you go about separating the information into fields? Let’s find out using our three Excel functions. Follow the steps below and use the above text string as a guide. Additionally, at the end of this article is a screenshot of an Excel sheet showing these formulas in action.

Step 1:

Open a new Excel workbook, copy and paste the data into column A, row 6 in a blank spreadsheet. Why row 6? Because, you should skip a few lines so you can place column headings, identifying information, etc. for future reference.

Step 2:

In Column B, row 6 use the LEFT function to isolate the first character. In the above example, it would be the letter “P”.

The format of the Left function is:  =LEFT(text, num_char)

Where:

TEXT is the text string that contains the characters you wish to isolate.
Num_Char is the number of characters you want to isolate starting from the left.
Therefore our first formula, entered into cell B6 of our conversion sheet is: =LEFT(A6,1).
The result will yield “P” in cell B6.

Step 3:

Now let’s extract the four character budget center using the MID function.
The format of the MID function is:  =MID(text, start_num, num_char)

Where:

TEXT is the text string that contains the characters you wish to isolate.
Start_Num is the starting position of the block of characters you wish to select.
Num_Char is the number of characters you want to isolate starting from the left.

Our second formula, entered into cell C6 is: =MID(A6,2,4). The result is “A425” in cell C6.
The above formula isolates a block of text four characters long starting with the second position in the string.

Step 4:

We will use the MID function again to isolate the seven character contract number. However, it may not be as easy as we first think. What if there are contract numbers shorter or longer than the one in the first entry represented in our example above? To plan for this contingency we will need to use the SEARCH function to calculate the proper number of characters. SEARCH simply returns the position of the target text.

The format of the SEARCH function is:  =SEARCH(target, in_text, start)

Where:

Target is the text you are searching for.
In_Text is the character string that contains the text you are looking for.
Start is the number of positions starting from the left from which to begin the search.

We will imbed the SEARCH function within the MID formula. Our third formula, entered in cell D6 is: =MID(A6,6,search(“-“,A6,1)-6). The result is “1234567” in cell D6.

The SEARCH formula isolates the position of the dash (-) within the text in cell A6 starting from the first character. The “-6” at the end tells Excel to select the six characters before the dash. Since we started at the sixth position, the result is a seven character selection.

Step 5:

To isolate the customer’s name, we will again use the MID and SEARCH functions together. This time, we want all characters after the dash. Here is the formula:

=MID(A6,search(“-“,A6,1)+1,100).  This formula simply selects the next 100 characters after the position of the dash within the text string. Using 100 ensures that customer names will not be cut off.

Step 6:

Now that you have entered all of the functions in the first line of the sheet, simply highlight the B6 to E6 range, select copy, and paste the formulas to all of the remaining lines containing the text strings you want to format. Your data is now properly formatted.

Step 7:

Now, highlight the entire Excel sheet by selecting the grey button in the upper right corner (i.e. above the row number “1” and to the left of column “A”). The entire sheet should have a black background. Select COPY, PASTE SPECIAL, VALUES. Then select OK.

The results calculated by your formulas have now been converted to values. You may now import or copy the results into your Access tables or Excel Applications.

Below is a screenshot of an Excel spreadsheet showing the formulas:

Now you have a few options:

1. You can add this data to an MS Access table in a much larger application and process it.
2. Use this information within a separate Excel workbook you have created.
3. Redisplay this information in a different configuration using the Ampersand operator (i.e. “&”), like this:=D6&”, “&C6&”, “&E6The result would be Contract#, Budget Center, and customer name as follows:
   
   1234567, A425, Acme Publishing Company