Need to creating Pairs with 4 digits number with leading 0.

[serge]

I have formulas that would create 6 pairs with a 4 digits number.

But when I have a number with leading zero or zeros I need to use a different formula, my question is :

I would like to use one formula to create the 6 different pairs , if possible ?.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1234		1	2	3	4		12	13	14	23	24	34
2
3	0001		0	0	0	1		00	00	01	00	01	01
4
Sheet2 (2)

Thanks for any help.
Serge.

 

[LxQ]

Start with a basic formula to make your number a four digit number:
=RIGHT("000"&A1,4)
this puts leading zeroes in front of the number, then cuts it down to the last four, so no matter what number you start with, it will end up with four digits

Then you can start breaking it up, the first digit will be:
=LEFT(RIGHT("000"&A1,4),1)

the second digit:
=MID(RIGHT("000"&A1,4),2,1)

to get the combination of first and second digit:
=LEFT(RIGHT("000"&A1,4),1)&MID(RIGHT("000"&A1,4),2,1)

This results in a text, so may have to put it into a VALUE() code

 

[serge]

Thanks LxQ, for the respond,

I wrote for :

the third digit:
=MID(RIGHT("000"&A1,4),3,1)

and the fourth digit:
=MID(RIGHT("000"&A1,4),4,1) Is that correct ?

Then your formula for the first pair you wrote :

=LEFT(RIGHT("000"&A1,4),1)&MID(RIGHT("000"&A1,4),2,1)

Then I wrote :

=LEFT(RIGHT("000"&A1,4),1)&MID(RIGHT("000"&A1,4),3,1)

and 'm lost for the rest ?? I'm not good enough to continue, would you be able to give me the rest of the formula please ?

Thank you.
Serge.

 

[LxQ]

You got it right for third and fourth digits, although since the fourth digit is the last one, you can simply use:

=RIGHT(A1,1)

For the combination of 1st and 3rd digits, you got it right:
=LEFT(RIGHT("000"&A1,4),1)&MID(RIGHT("000"&A1,4),3,1)

Then 1st and 4th:
=LEFT(RIGHT("000"&A1,4),1)&RIGHT(A1,1)

2nd and 3rd:
=MID(RIGHT("000"&A1,4),2,1)&MID(RIGHT("000"&A1,4),3,1)

2nd and 4th:
=MID(RIGHT("000"&A1,4),2,1)&RIGHT(A1,1)

3rd and 4th:
=MID(RIGHT("000"&A1,4),3,1)&RIGHT(A1,1)

 

[serge]

Hi LxQ,

Thank you very much for the formulas they work perfectly fine for my file.
I really appreciate your help.

Serge.

 

[Scott Huish]

If it's just for display purposes, you could just coerce the values to numbers and custom format the result cells as 00:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	1234		1	2	3	4		12	13	23	34
2	0001		0	0	0	1		00	00	00	01
Sheet1

 

[serge]

Hi HOTPEPPER,

Thank you for the reply, but I don't get the same result than your, I did it a second time on a new sheet and the same !!!

Row 1 work fine but row to is not ?

C2 : 1
D2 : F2 empty cells
H2 : 1
I2 : 1
J2 : #VALUE!
K2 : #VALUE!

What am I doing wrong ?
Thanks.

 

[LxQ]

If you want to list the digits in columns C through F, you can use the formulas for each digit:
for C2: =LEFT(RIGHT("000"&A1,4),1)
and so on..

then in H2, you can just do =C2&D2

 

[LxQ]

...What am I doing wrong ?

I tried those, I didn't get the right answers either on that.. if you have 1 in A, the first clue that something didn't work was when the 1 showed up in C instead of F... somehow it's not adding the leading 0's

 

[serge]

Thank you LxQ for the reply, and the formula, I'm gone wait for HOTPEPPER, maybe he would have and answer to his formulas ? I would like to see what he think, why it doesn't work, to learn a little bit.

Serge.