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

 

[phantom1975]

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

 

[Joe4]

Marc,

Using the suggestions above, you should be able to do what you want. If you do not wish to show the "intermediate" 7 digit column, you could combine it with your statement.

An example to pull the leftmost 3 digits from your original data:

=LEFT(TEXT(A2,"0000000"),3)

(assuming your data begins in A2)

 

[Marc]

My apologies to everyone above, I wasn’t referencing my LEFT function correctly. It all works. Another one of those cases where the simple solution just couldn’t be seen right in front of my two eyes. Cheers!!