Changing fixed-format text to numbers

Last week I talked about ways to use automatic formatting to convert text to numbers. To do that, you need to have the data coming from outside, somehow – either being typed in, or imported, or pasted in from a non-spreadsheet. If you have numeric text in a spreadsheet already, here’s how you can convert it to numbers without leaving the spreadsheet.

For all of these options, you’ll want to use a blank column right next to the column with the text you’re trying to convert. Assuming the text is in column A, starting in A1, then enter the conversion formula in B1, and copy/paste it down the column until you reach the last thing you want to convert.

Option 1: Divide the number by one (1).
This will only work if the number you’re pulling from looks like a number. Basically, if your spreadsheet would have auto-formatted it into a number if you’d entered it, then dividing it by one will invoke the same set of rules it would have used to do the auto-formatting. You could also add zero, or pretty much any other identity function.

Option 2: Extract the numbers, then divide by 1.
For example, if you had born-died years, or start-end times, you’d want to extract them into two columns: one to hold each number.

A1
Birth & Death Dates
B1
Birth
C1
Death
1732-1799 =left(a1,4)/1 =right(a1,4)/1
b:1732, d:1799 =mid(a1,3,4)/1 =right(a1,4)/1
(1732 to 1799) =mid(a1,2,4)/1 =mid(a1,10,4)

Basically, in these examples the right(), left(), and mid() functions pull out the 4 digits of the appropriate year, then dividing by 1 turns it into a number.

Option 3: Extract the digits then recombine them or use math to build the number.
If you’ve got (123) 456-7890 then your goal is probably a single column with the entire phone number, not three columns with the area code, first three, and last 4 digits respectively. Therefore you need to pull out the numbers, then recombine then into a single number. You can either recombine them as text or recombine them using math, whichever you’re most comfortable with. Here’s some examples.

Original (A1) Formula
(123) 456-7890 =concatenate(mid(a1,2,3), mid(a1,7,3), right(a1,4))/1
Breakdown: mid(a1,2,3)=”123″
mid(a1,7,3)=”456″
right(a1,4)=”7890″
concatenate(“123″,”456″,”7890″)=”1234567890”
“123456789”/1=123456789
123-45-6789 =concatenate(left(a1,3), mid(a1,5,2), right(a1,4))/1
Breakdown: mid(a1,2,3)=”123″
mid(a1,5,2)=”45″
right(a1,4)=”6789″
concatenate(…)=”123456789″
“123456789”/1=123456789
5.01 E+3 =left(a1,4)*10^right(a1,1)
Breakdown: left(a1,4)=”5.01″
right(a1,1)=”3″)
10^”3″=1000
“5.01”*1000=5010
5 1/2 =left(a1,1)+mid(a1,3,1)/right(a1,1)
Breakdown: right(a1,1)=”2″

mid(a1,3,1)=”1″

“1”/”2″=.5

left(a1,1)=”5″

“5”+.5=5.5

If you’re wondering why I never divided by 1 in last two, it’s because I was doing a different math function in them. And function like that will work.

You might have also noticed that all of these formulas depend on knowing how many digits there are, and where they’re located. Next week I’ll talk about how to handle it when you don’t have that information.

Changing text to numbers automatically

Back when I was first talking about how to do text formatting, I said you could do it all from a single menu/window.  That’s still true of number formatting, but unlike any other kind of formatting, number formatting can be done by typing in the cell.

To invoke automatic number formatting, two things need to be true.  First, there must not be an existing number format on the cell.  Second, the number you’re entering must look like a valid number format to the program. Continue reading Changing text to numbers automatically

Boolean values

In case you’re not already familiar with the term, Boolean values are true and false. They can also be represented as yes/no, on/off, or 1/0. There’s also Boolean searching, which is based on Boolean logic, but that’s a post all by itself. Suffice it to say that when you’re doing Boolean logic you’re applying rules that will get you a Boolean value as a result. Simplistically, when you do a search on the Internet the search engine goes through its list of websites and checks to see if each one matches your search. If it does, that match is “true” and the site gets added to your list of results. If it doesn’t match then the test returns “false”, and you never see the site it was testing. Obviously it’s more complicated than that, and tries to judge how well sites match and give you the best ones first, but the root of it all is just Boolean logic.

Generally, if you want a Boolean value in a spreadsheet you have to either type true or false, or use a function that returns true or false. No matter how you capitalize it, it will convert it to TRUE or FALSE. Do not put quotes around it or before it, and if you misspell it or have a space after it or anything like that, it won’t work.

However, even though it will display TRUE or FALSE, Boolean values in spreadsheets are (sort of) numbers. You can add, subtract, divide, and multiply them with each other or with other numbers. False has a value of zero, and true has a value of 1. This means that TRUE+TRUE=2, TRUE-FALSE=1, and TRUE*FALSE=0. And when I say TRUE-FALSE=1 I do mean that it will return the number 1. In most programs it won’t be formatted as a Boolean value anymore, and there’s no way to force it to look like a Boolean value.

This is because most programs don’t have Boolean as a number format. Of the ones I tested only OpenOffice Calc does. In OpenOffice, if you format a number as Boolean then 0 is false and anything else is true. This means that TRUE-TRUE=TRUE and TRUE-TRUE=FALSE could both be true, since 2-1=1 and 1-1=0. This confusion may be why most programs don’t allow it. In fact, most programs probably only store Boolean values as numbers because they take up less space when saved that way (1 bit vs 4 or 5 bytes), and back when the ground rules were being established disk space was at a premium. They never expected people to treat it as a number.

The reason why I said they’re only sort of numbers is that, except in OpenOffice Calc, sometimes they’re treated as numbers, and sometimes they’re treated as text. TRUE+FALSE=1, but concatenate(TRUE,FALSE)=”TRUEFALSE” in most programs, and “10” in OpenOffice. Note how I put quotes around “TRUEFALSE” and “10” but not around TRUE and FALSE. That’s because “TRUEFALSE” is now completely text, and “10” is in an ambiguous state. As it is, it’s text. If you ask the program whether it’s a number, it will say no. If you sorted a column with 10 and “10” in it, the number would be sorted before the text was, and so on. However it’s still possible to convert it back to a number, and do math on it. There’s lots of options for doing this, depending on the exact format in question, so next week I’ll start discussing how to convert between text and numbers for all of the common number formats.