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

#### serge

##### Well-known Member
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
ABCDEFGHIJKLMN
112341234121314232434
2
300010001000001000101
4
Sheet2 (2)

Thanks for any help.
Serge.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=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

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.

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)

Hi LxQ,

Thank you very much for the formulas they work perfectly fine for my file.

Serge.

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
ABCDEFGHIJK
11234123412132334
20001000100000001
Sheet1

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.

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

...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

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.

Replies
4
Views
132
Replies
1
Views
116
Replies
3
Views
249
Replies
10
Views
282
Replies
2
Views
238

1,203,186
Messages
6,053,984
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back