Seven Digit Number with Leading Zero's

[Marc]

Hello Everyone, I have policy numbers given to me in a spreadsheet of various lengths. We use 7 digit policy numbers here and I need to convert these feed numbers to 7 digit numbers so that I can further break them down into three columns containing the “first 3”, “second 2” and “last 2” of the 7 digit number.

The best solution that I could imagine was to use this rather long if function to get the 7 digits and then use it to produce my three columns. The function just determines the length of the feed number and appends the appropriate amount of zero’s to the front.

=IF(LEN(A2)=1,"000000"&A2,IF(LEN(A2)=2,"00000"&A2,IF(LEN(A2)=3,"0000"&A2,IF(LEN(A2)=4,"000"&A2,IF(LEN(A2)=5,"00"&A2,IF(LEN(A2)=6,"0"&A2,IF(LEN(A2)=6,"0"&A2,A2)))))))

I can’t simply just format the feed column, because that will not allow me to produce the three columns. The function will work, I was just wondering if anyone has a better solution. Regards, Marc

7 digit policy number.xls
	A	B	C	D	E
1	FeedNo.	7Digits	First3	Middle2	Last2
2	1	0000001	000	00	01
3	12	0000012	000	00	12
4	123	0000123	000	01	23
5	1234	0001234	000	12	34
6	12345	0012345	001	23	45
7	123456	0123456	012	34	56
8	1234567	1234567	123	45	67
Sheet1

 

[LTunnicliffe]

If it doesn't have to be a number then =text(A1,"0000000") should work for you.

Hope this helps!

 

[Mark W.]

Book1
	A	B	C	D	E	F
1	1	0000001
2	12	0000012
3	123	0000123
4	1234	0001234
5
6
Sheet7

 

[nevillestoke]

Why not just use Format, Custom, to 0000000 rather than the lengthy formula? Then you can split them form there - possbily with Data | Text tio Columns.

Give it a try.

 

[Joe4]

nevillestoke,

Changing the Format only changes the appearance, not the underlying values. Add then taking it to Text to Columns will not change that.

You actually need to convert the data to text with the leading zeroes, like what LTunnicliffe suggests.

 

[nevillestoke]

jmiskey

thanks for setting me straight on this one I continue to be amazed at the amount of stuff available on this forum :idea: !

Neville

 

[Seti]

Try this formula:

=RIGHT("0000000"&A2,7)

 

[phantom1975]

Give this a shot:

Book1
	A	B	C	D
1	1	1000000
2	22	2200000
3	333	3330000
4	4444	4444000
5	5555	5555000
6	666666	6666660
7	5465465465	5465465465
Sheet1

 

[Marc]

Thanks for the offerings everyone. I’ve attached what they produce below. The problem I have with the various solutions is that when I go to produce my column to get the first 3 digits of the seven digit policy number I’m not getting the first 3 characters(digits) when referencing the 7 digit solutions.

Phantom, You offer something that looks like it could work, but I could not get it to make the zero’s become leading zero’s. Again Thanks All, Marc

PS. If converting the given feed number to anything will enable a solution I can go with that. It doesn’t have to be a number.

7 digit policy number.xls
	A	B	C	D	E
1	FeedNo.	7Digits	First3	Middle2	Last2
2	1	0000001	000	00	01
3	12	0000012	000	00	12
4	123	0000123	000	01	23
5	1234	0001234	000	12	34
6	12345	0012345	001	23	45
7	123456	0123456	012	34	56
8	1234567	1234567	123	45	67
9
10	LtunnicliffeandMarkW.Offering
11	1	0000001	1	00	01
12	12	0000012	12	00	12
13	123	0000123	123	01	23
14	1234	0001234	123	12	34
15	12345	0012345	123	23	45
16	123456	0123456	123	34	56
17	1234567	1234567	123	45	67
18
19	SetiOffering
20	1	0000001	1	00	01
21	12	0000012	12	00	12
22	123	0000123	123	01	23
23	1234	0001234	123	12	34
24	12345	0012345	123	23	45
25	123456	0123456	123	34	56
26	1234567	1234567	123	45	67
Sheet1

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1		7Digits	First3	Middle2	Last2
2	1	0000001	000	00	01
3	12	0000012	000	00	12
4	123	0000123	000	01	23
5	1234	0001234	000	12	34
6	12345	0012345	001	23	45
7	123456	0123456	012	34	56
8	1234567	1234567	123	45	67
9
10
11
12
Sheet10