Help? Can't seem to split to digits well!

[Noobachu]

Would someone please help? I have a column of data values between 4 and 7 digits in length. I need to split them into separate columns so that they line up on the right, with blanks placed for non-existant significant figures; that is, the numbers
1111678 and
1234
will have 8 and 4 in a column, 3 and 7 in a column, 6 and 2 in a column, 1 and 1 in a column, and blanks for the ten-thousand, hundred-thousand, and millions places for the row containing 1234. It's important that I don't have leading zeros (I was able to do it with MID() and Text(), but it gave me leading zeros) so that I can count the number of zeros accurately in, say, the hundred-thousands place.
Thanks!
~A Noob

P.S. I'm using Excel for Mac, 2008.

 

[p45cal]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H
1	1111678	1	1	1	1	6	7	8
2	1234				1	2	3	4
Sheet

Now you can copy them down.
If you have xl2007 or later this can be shorter.

It's cumbersome to manually alter the -6,-5,-7 etc. on the first row so you could tweak so that you can copy across and down from just one cell:

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>=IF(ISERROR(MID($A1,LEN($A1)+COLUMN()-8,1)),"",MID($A1,LEN($A1)+COLUMN()-8,1))</td></tr></table></td></tr></table>

 

[mikerickson]

Perhaps this formula would do. Put it in a row 1 cell and drag right 8 cells and down as far as needed.

=MID(RIGHT(TEXT($A1,REPT(" ",7)&0),7),COLUMN(A2),1)

(I would have used seven spaces explicitly instead of the REPT, but that wouldn't show clearly on the forum.)

 

[Noobachu]

Thanks, Mikerickson, that worked perfectly!