Changing text to numbers, and numbers to text (about)

Changing text to numbers
Sometimes numbers in a spreadsheet aren’t actually numbers. They might look like numbers to you, but to the spreadsheet they’re just a bunch of characters that happen to be numerical digits. This is relevant because you can’t do math on non-numbers.

There are lots of reasons why this could happen. One of the most common ways for this to happen is when someone has made a table with a column which contains more than one piece of numerical information. For example, a table listing famous people might have their birth and death dates listed as born-died (e.g. 1732-1799) instead of having one column for the birth year, and a separate one for their death year. Or, in a schedule, you might have 11:30-12:30 for a meeting running from 11:30 to 12:30. Or a citation might have pages 45-57.

It can also be because you’re providing more than one piece of the same type of information. For example, you might have only a single column for ISBN. That way you don’t have to check two places to see if you have the ISBN for a given book, but if you happen to have both the ISBN-10 and ISBN-13 you might put them both in, with a comma between them. Obviously, “3639420144, 978-3639420142″ is not a number.

Regardless of how it happened, there’s a number of reasons you might want to convert the data to actual numbers. Maybe you want to be able to calculate how many pages are in each article of a list. Maybe you want to be able to sort a list of dates so that 1/1/2012, 2/2/2010, and 3/3/2011 are actually in chronological order. Fortunately, it’s very possible to do so. How you do it just depends on how far from a “normal” number the text is, and how consistent it is.

Broadly, if it looks just like a number, you can use automatic number formatting to turn it into an actual number. I’ll discuss how automatic number formatting works next week.

If it’s a number along with something else, then you’ll need to extract the numbers using text manipulation, then convert the numeric digits to numbers using the fact that any simple math equation, such as dividing by one or adding zero, will convert them into a genuine number. If the numbers all have exactly the same format, such as dates where each month and day have two digits (01/01 through 12/31), then the functions I’ve already talked about (right, left, mid, and concatenate) will be enough to do the extraction. If the format varies however, such as when the dates only have as many digits as they need (1/1 through 12/31), then you’ll need a couple of new functions that I’ll talk about week after next.

Changing numbers to text
The flip side is that there are also a lot of reasons why you might want to convert a number to text. In my experience, most of them have to do with either making it appear a certain way, which the number formats don’t allow, or else combining two or more columns of numbers into a single column.

The basic tool to use is concatenate, but it’s important to remember that it will turn the actual number itself into text. $1, 100%, 1.00, 1E0, and 1/1/1900 are all just the number 1 with different formats applied, so if you used concatenate on them you’d get “1” for all of them.

If you just want to add a little extra, you still only use concatenate. If the number in question was in a1 you’d just use concatenate(“$”,a1), concatenate(a1,”00%”), etc. If you want to take some away, though, it gets more complicated – you’ll need to use multiplication, division, modulo division, and rounding. And if you want to completely change it, which you’ll want to do if you’re trying to turn a date into text, then you’ll need to use date and time functions. These are all things I’ll be covering in the weeks to come.