Grouped by fixed numbers

[motilulla]

Hello,</SPAN></SPAN>

Column "B" contains the codes, in the column "C" codes are grouped by in the friction of (1 to 2187) (2188 to 4374) and so on, numbers.</SPAN></SPAN>

Example B4=100 are in the group from 1 to 2187, and the B5=3250 is in the group from 2188 to 4374 Note: to get these group frictions result I am using following formula.</SPAN></SPAN>

The Codes are from 1 to 7 digits say example 1 to 3.000.000 so far I am looking the formula the can the can give me the results in 7 digit as shown in the column "D (adding 0's and making the result in 7 digits as the result of B4 In D4 (not as C4)</SPAN></SPAN>

Example...</SPAN></SPAN>

Book1
	A	B	C	D
1
2		Codes	Grouped By 2187	Requited Result With 7 Digits
3		Codes	From - To	From - To
4		100	1 - 2187	0000001 - 0002187
5		3.250	2188 - 4374	0002188 -0004374
6		9.980	8749 - 10935	0008749 - 0010935
7		10.790	8749 - 10935	0008749 - 0010935
8		19.875	19684 - 21870	0019684 - 0021870
9		38.899	37180 - 39366	0037180 - 0039366
10		97.076	96229 - 98415	0096229 - 0098415
11		15.478	15310 - 17496	0015310 - 0017496
12		37.038	34993 - 37179	0034993 - 0037179
13		899.000	898858 - 901044	0898858 - 0901044
14		748.341	747955 - 750141	0747955 - 0750141
15		184.326	183709 - 185895	0183709 - 0185895
16		200	1 - 2187	0000001 - 0002187
17		7.630	6562 - 8748	0006562 - 0008748
18		102.522	100603 - 102789	0100603 - 0102789
19		1.235.689	1235656 - 1237842	1235656 - 1237842
20		2.176.985	2176066 - 2178252	2176066 - 2178252
21		1.000.005	999460 - 1001646	0999460 - 1001646
22		867.789	866053 - 868239	0866053 - 0868239
23		39.000	37180 - 39366	0037180 - 0039366
24		52.500	52489 - 54675	0052489 - 0054675
Sheet1

Cell Formulas
Range	Formula
C4	=INT((B4-1)/2187)*2187+1 & " - " & INT(((B4-1)/2187)+1)*2187

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>

 

[cerfani]

try TEXT(A1,"0000000")

replace A1 with a number or reference to a number

 

[motilulla]

try TEXT(A1,"0000000")

replace A1 with a number or reference to a number

Hello cerfani, I can't get the result as I needed in the column "D"</SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN>
Moti</SPAN></SPAN>

 

[kweaver]

Try this in D4 and fill down:

=TEXT(LEFT(C4,FIND(" ",C4)-1),"0000000")&" - "&TEXT(RIGHT(C4,LEN(C4)-FIND("-",C4)),"0000000")

 

[motilulla]

Try this in D4 and fill down:

=TEXT(LEFT(C4,FIND(" ",C4)-1),"0000000")&" - "&TEXT(RIGHT(C4,LEN(C4)-FIND("-",C4)),"0000000")

Wow kweaver, it worked 100% totally solved!</SPAN></SPAN>

I appreciate your help have a great weekend.</SPAN></SPAN>

Kind Regards,</SPAN>
Moti</SPAN> </SPAN>

 

[Fluff]

You could also use your original formula like
=TEXT(INT((B4-1)/2187)*2187+1,"0000000") & " - " & TEXT(INT(((B4-1)/2187)+1)*2187,"0000000")

 

[motilulla]

You could also use your original formula like
=TEXT(INT((B4-1)/2187)*2187+1,"0000000") & " - " & TEXT(INT(((B4-1)/2187)+1)*2187,"0000000")

Excellent Fluff, your formula is straight and more efficient I will go with your formula</SPAN></SPAN>

I appreciate your help have a great weekend.</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Fluff]

You're welcome & thanks for the feedback