Numbers

[elgringo56]

I have a cell that has a number in it, it could be any number from 1 to 99,999,999. I have set up 8 cells, A1 thru H1. I would like to put the least significant digit in cell H1, second least significant digit in Cell G1 and so on back to the most significant digit, it it is a number that large, in Cell A1. Does anyone know how I can do this with forumulas instead of macros?

 

[Juan Pablo González]

I'm not sure what you mean by "most significant digit", but I feel that you need the RANK formula.

 

[elgringo56]

When I went to school, the most significant digit was the digit all the way to left, the least significant digit is the one on the right. I will look up the Rank and see what it is, thank you.

 

[elgringo56]

I dont think that is it. That returns the rank of a number within a list of numbers. I have a single number. I want to put each digit of that number in a cell, the left most number in Cell H1, the tens column number in Cell G1, the hundreds column number in Cell F1 and so on back. I know with text you can use the LEFT or RIGHT function to pull out characters, but I dont know how to do it with numbers. Any ideas?

 

[Juan Pablo González]

Put this formula in A1 and drag to the left

=MID(TEXT($A$2,"00000000"),COLUMN(A1),1)

or enter this array formula in A1:H1

{=MID(TEXT($A$2,"00000000"),COLUMN(A1:H1),1)}

I assumed the number is in A2.

 

[PaddyD]

Juan,

If I get the OP right, I don't think your version will work - if you want significant digits, the number 9's most significant digit is 9, as is 98,765,432. Your formula would match the number 9's 9 with 98,765,432's 2. There are no doubt array approaches to this, but I don't think you can avoid a test for the lenght / size of the number somewhere:


see the next post for the worksheet

Paddy
This message was edited by PaddyD on 2002-08-27 18:25

 

[PaddyD]

Juan's version:

Book5
	A	B	C	D	E	F	G	H	I
1	Juan'sVersion:
2		SG1	SG2	SG3	SG4	SG5	SG6	SG7	SG8
3	9	0	0	0	0	0	0	0	9
4	98	0	0	0	0	0	0	9	8
5	987	0	0	0	0	0	9	8	7
6	9,876	0	0	0	0	9	8	7	6
7	98,765	0	0	0	9	8	7	6	5
8	987,654	0	0	9	8	7	6	5	4
9	9,876,543	0	9	8	7	6	5	4	3
10	98,765,432	9	8	7	6	5	4	3	2
11
Sheet2

 

[PaddyD]

Paddy's

Book5
	A	B	C	D	E	F	G	H	I
12	Paddy's:
13		1	2	3	4	5	6	7	8
14	9	9
15	98	9	8
16	987	9	8	7
17	9,876	9	8	7	6
18	98,765	9	8	7	6	5
19	987,654	9	8	7	6	5	4
20	9,876,543	9	8	7	6	5	4	3
21	98,765,432	9	8	7	6	5	4	3	2
22
Sheet2

 

[elgringo56]

Paddy, that is almost right, but, it is reversed. a single 9 would be in H1, if its 98, then the 8 would be in H1 the 9 in G1, for the 987, the 7 should be in H1, the 8 in G1, the 9 in F1 and so on. Thanks to both of you, I hope I can figgure out how to turn it around.

 

[elgringo56]

I just read it a little closer, I think Juans is the right one. I will play with them both