Text Manipulation In Microsoft Excel - Three Common Issues and Solutions

Whether you are querying a database, programming in C Sharp or analyzing data in Microsoft Excel, it is important to be aware of data types. There are many data types and the ones encountered most often are Text, Number and Date. Being able to recognize data types and convert them to the desired type will allow you to get on with the more important matter of using the data.

Why Won’t My Formula Add These Numbers?

This is a question I’ve encountered many times. A series of numbers in column D needs to be added but the SUM formula in cell D17 returns zero. If your data is text, even if it appears to be a number, you won’t be able to use it in a calculation.

In the absence of formatting, text aligns to the left side of a cell and numbers align to the right, a clue to the data type in column D.

There are two common approaches to converting text to number. The first is to put a formula in cells E2 to E16 that adds zero to each corresponding number in column D (or multiply each number by one). When you perform math with text that appears to be a number, Excel performs the conversion and the result will be numbers in column E that can be added.

The second approach is to click on the File tab, select Options and then click on the Formulas tab. Make sure that Enable background error checking is checked then click OK to close Options. There will now be a little green marker in the upper-left corner of each cell in column D. The marker indicates an “error”. If you select one cell or all of the cells in column D, a smart tag will appear and clicking on it will offer a selection to Convert to Number. After the conversion, the addition can take place.

How Do I Convert This Text String To A Date?

A more complicated conversion is from Text to Date. Dates can be represented in many different ways using text and sometimes the year, month and day positions can be confusing. Looking at cell C2 in the data, we need a formula in cell E2 to extract the year, month and day. Start the formula with an extraction of the month, character positions 5 and 6. The MID function can extract text from the middle of a string. =MID(C2,5,2) will extract 06 from the string in C2 by starting the extraction at character 5 and returning a length of 2 characters. Next, join the forward slash character to this string being built, the formula should be: =MID(C2,5,2)&"/" and it will return 06/. The & character is used to join text strings together. The next text string to join is the day portion of the date, in character positions 7 and 8. You could use MID(C2,7,2) but you can also use the RIGHT function to return 2 characters from the right side of the string. The formula should now be: =MID(C2,5,2)&"/"&RIGHT(C2,2) and the result is 06/11. Another forward slash should be joined to the formula before the year portion of the date is added. The MID function could be used to extract the year from the first position with a length of four characters, but the LEFT function can also be used to extract text from the left side of the string. The formula is now =MID(C2,5,2)&"/"&RIGHT(C2,2)&"/"&LEFT(C2,4) and returns 06/11/2013. Unfortunately the result is another text string, but the conversion to a date is as simple as surrounding the formula in parentheses and then adding zero. The final formula is: =(MID(C2,5,2)&"/"&RIGHT(C2,2)&"/"&LEFT(C2,4))+0 and the result is 41436, which is exactly what we want. Format this cell with the Number Format drop-down menu on the Home tab. Excel represents June 11, 2013 with the number 41436 (the number of days starting from January 1, 1900) and we can now add or subtract days from this cell to perform date math. This formula can now be copied downwards for as many rows as necessary.

It should be noted that my computer uses a Month-Day-Year format to interpret dates, so 02/03/2012 is February 3, 2012. Another computer with different Windows Regional Settings may interpret this as March 2, 2012, so make sure your conversion gives the desired result. This approach will achieve the desired result in virtually every conversion from Text to Date.

I Don’t Like The Number Format In My Message

After data modeling is complete, it is often desirable to join the results to text strings for charts, visualizations or Infographics. For example, suppose we want to use the sum of column D to calculate a 3% administration charge and display this in a chart message. Since the sum of the invoices appears in cell D17, we can place the message in cell D18 using the formula
="Admin Charge is "&0.03*D17 which displays the resulting message as follows:

No formatting was applied to the calculation before converting it to text. Use the TEXT function to control conversions of dates or numbers to text. The formula should be updated to
="Admin Charge is "&TEXT(0.03*D17,"$#,###.00") to display the following result:

The TEXT formula requires the calculation in the first argument and a format to use when converting to text in the second argument. The format used here is a dollar symbol, number placeholders with a comma to allow thousands separation, a period then two mandatory digits after the decimal for cents.

If you are interested in learning more about converting and formatting data types, let me show your organization how it can improve its Excel models. If you need a data model, database solution or analytics training, please contact Scott Cuthbertson:

Email: scottjcuthbertson@gmail.com
LinkedIn: http://ca.linkedin.com/in/scottcuthbertson
Blog: http://theanalyticspro.blogspot.ca/

Scott Cuthbertson BBA

Scott is an Analytics Professional who has worked with over 100 clients in the retail and financial services industries. He has helped organizations improve their labour scheduling, inventory replenishment, performance management, organization design and pricing. He has been a practitioner of analytics since childhood, using data models to win Hockey Pools. His expertise lies in developing data models, reporting, algorithms and solutions for any organization that wants to use their data to improve decision making. He also teaches at the CMA Professional Development Institute.

© Scott Cuthbertson 2014 Blog: Words From The Analytics Pro