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.