Separate Numbers into Digits with Excel
- Mar 24, 2009 7:00 PM EST
- [num] Comments
Q: I have a set of four- and five-digit numbers in one column, and I want each digit to be in a different column. How can I do it? - Joel Garcés.
A: Excel has a number of functions that work to pick out specific portions of text strings. LEFT() returns the specified number of characters starting at the left and RIGHT() does the same starting at the right. MID() reaches into the middle of the string at the specified location and pulls out the number of characters you request. MID() is what you need in this case.
First, though, you'll have to make the input numbers consistently five digits long. That's easily accomplished with the TEXT() function, which formats the data passed to it using whatever format string you like. In this case, =TEXT(A2,"00000") would return the number in cell A2 left-padded with zeroes to a full five digits.
The COLUMN() function returns the column number of the current cell, just as ROW() returns the row number. We can use that to choose which digit the MID() function will return. Assuming the numbers run down column A starting in A2, put this formula in B2: =MID(TEXT(A2,"00000",COLUMN()-1,1) . Copy it out to column F and copy it down columns B through F as far down as the original numbers go. You've got them separated into digits.
One little refinement will be needed if you want to perform calculations using those digits. Total any of the digit columns and you'll get a big fat zero because Excel treats the digits as text, not numbers. The solution is simple - wrap the whole formula in B2 in a VALUE() function like so: =VALUE(MID(TEXT($A2,"00000"),COLUMN()-1,1)). Now copy it over the rest of the formulas. You now have the desired result, and you've learned some interesting tricks for manipulating text in Excel. _ Neil J. Rubenking.