Convert random 3-digit number to it's smaller 3-digit boxed form

[mft38Excel]

I need to convert a 3-digit straight number to it's smaller 3-digit boxed number counterpart. There are 1,000 straight numbers in sequential order from 0 to 999. There are only 220 boxed numbers. Boxed numbers are unique in that their 3-digits are mostly in ascending sequential order, like 123 or 567. The exceptions are triplets, like 000 and 999 which have no order. The formula should match or find the boxed number that goes with the straight number.

 

[Peter_SSs]

Welcome to the MrExcel board!

From your description and image, the problem is still not clear to me.

There are 1,000 straight numbers in sequential order from 0 to 999.

The only sequential numbers that I can see in your image are all 4-digit numbers, not 3.

There are only 220 boxed numbers. Boxed numbers are unique in that their 3-digits are mostly in ascending sequential order, like 123 or 567. The exceptions are triplets, like 000 and 999 which have no order.

I don't understand the 220
3 digit numbers with digits in sequential ascending order by my calculations are only
012
123
234
345
456
567
678
789

Triplets are
000
111
222
333
444
555
666
777
888
999

That is 18 altogether in those two lists, not 220.

Clearly I have not understood.
So, can you try explaining again and perhaps give a sample of data and expected results with XL2BB?

 

[Saurabhj]

Hi, Please check below:

Straight numbers are in text format.

Book1
	E	F
1	000	000
2	150	015
3	510	015
4	051	015
5	105	015
6	501	015
7	015	015
8	330	033
9	033	033
10	303	033
11
Sheet1

Cell Formulas
Range	Formula
F1:F10	F1	=SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),1)&SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),2)&SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

Does this normally entered formula do what you want...

=MIN(B2:D2)&MEDIAN(B2:D2)&MAX(B2:D2)

 

[mft38Excel]

Welcome to the MrExcel board!

From your description and image, the problem is still not clear to me.


The only sequential numbers that I can see in your image are all 4-digit numbers, not 3.


I don't understand the 220
3 digit numbers with digits in sequential ascending order by my calculations are only
012
123
234
345
456
567
678
789

Triplets are
000
111
222
333
444
555
666
777
888
999

That is 18 altogether in those two lists, not 220.

Clearly I have not understood.
So, can you try explaining again and perhaps give a sample of data and expected results with XL2BB?

My apologies for insufficient description Peter_SSs. The 3-digit number [straight] that I needed to convert was in two formats within the table. First I had the 3-digits in separate fields, "strai_1", "strai_2," and "strai_3". Next I had the 3-digit number in one field, "straight". The "seq_no" was a row count only. The "Sample" table to the lower right showed the "straight" numbers that needed to be converted to the "boxed" numbers. The actual table I will use the solution in has 1,000 straight numbers in ascending sequential order.
The best solution for my purpose is given below.
Thank you,

 

[mft38Excel]

Does this normally entered formula do what you want...

=MIN(B2:D2)&MEDIAN(B2:D2)&MAX(B2:D2)

Greetings Rick,
Yes, this is the best solution for my purpose. I enter the formula in my "boxed" field, G2, and copy to G16. Formula takes the individual digits and arranges them in ascending order [horizontally]. Thank you, Rick

 

[mft38Excel]

Welcome to the MrExcel board!

From your description and image, the problem is still not clear to me.


The only sequential numbers that I can see in your image are all 4-digit numbers, not 3.


I don't understand the 220
3 digit numbers with digits in sequential ascending order by my calculations are only
012
123
234
345
456
567
678
789

Triplets are
000
111
222
333
444
555
666
777
888
999

That is 18 altogether in those two lists, not 220.

Clearly I have not understood.
So, can you try explaining again and perhaps give a sample of data and expected results with XL2BB?

Peter,
This formula from Rick below meets my need.

=MIN(B2:D2)&MEDIAN(B2:D2)&MAX(B2:D2)

I enter the formula in cells G2:G16 and it converts the straight number to a boxed number in ascending order [horizontally].

Thank you, Peter

 

[mft38Excel]

Hi, Please check below:

Straight numbers are in text format.

Book1
	E	F
1	000	000
2	150	015
3	510	015
4	051	015
5	105	015
6	501	015
7	015	015
8	330	033
9	033	033
10	303	033
11
Sheet1

Cell Formulas
Range	Formula
F1:F10	F1	=SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),1)&SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),2)&SMALL(VALUE(MID(E1,ROW(INDIRECT("1:" &LEN(E1))),1)),3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Saurabhj,​

Thank you for the formula. You produced the results I was looking for. I noted that your formula started with the "Straight numbers are in text format". I entered the formula in cell F2 but I received the #VALUE! error instead of the result. I changed my integer straights [E2:E16] to Excel General format then to Text format but then received the #NUM! error. I took the options to Evaluate Formula and it shows problem with the first VALUE. I also used the keystroke, CTRL+SHIFT+ENTER.

 

[Fluff]

Another possible option (although Rick's is simpler)

=TEXT(SUM(10^{2,1,0}*AGGREGATE(15,6,MID(TEXT(E2,"000"),{1,2,3},1)+0,{1,2,3})),"000")

 

[mft38Excel]

Saurabhj,​

Thank you for the formula. You produced the results I was looking for. I noted that your formula started with the "Straight numbers are in text format". I entered the formula in cell F2 but I received the #VALUE! error instead of the result. I changed my integer straights [E2:E16] to Excel General format then to Text format but then received the #NUM! error. I took the options to Evaluate Formula and it shows problem with the first VALUE. I also used the keystroke, CTRL+SHIFT+ENTER.

Saurabhj,

I just installed XL2BB to capture my Excel table. Following are the results I received with your formula.

3-Digits-Straights-convert-to-Boxed.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	seq_no	strai_1	strai_2	strai_3	straight		boxed
2	0001	4	1	0	410	#NUM!	014
3	0002	0	0	0	0		000
4	0003	0	1	5	15
5	0004	0	2	4	24
6	0005	0	3	3	33
7	0006	0	4	2	42
8	0007	0	5	1	51		015
9	0008	0	6	0	60
10	0009	1	0	5	105
11	0010	1	5	0	150
12	0011	3	0	3	303
13	0012	3	2	1	321
14	0013	3	3	0	330
15	0014	5	0	1	501
16	0015	5	1	0	510
17										Sample
18					straight	boxed				straight	convert to	boxed
19					150	=SMALL(VALUE(MID(E19,ROW(INDIRECT("1:" &LEN(E19))),1)),1)&SMALL(VALUE(MID(E19,ROW(INDIRECT("1:" &LEN(E19))),1)),2)&SMALL(VALUE(MID(E19,ROW(INDIRECT("1:" &LEN(E19))),1)),3)				150		015
20					510					510		015
21					051					051		015
22					105					105		015
23					501					501		015
24					015					015		015
25					330					330		033
26					033					033		033
27					303					303		033
28
29
30
31
32	This works.
33	seq_no	strai_1	strai_2	strai_3	straight		boxed	Results
34	0001	4	1	0	410		014	014
35	0002	0	0	0	000		000	000
36	0003	0	1	5	015			015
37	0004	0	2	4	024			024
38	0005	0	3	3	033			033
39	0006	0	4	2	042			024
40	0007	0	5	1	051		015	015
3 straights to 3 boxed

Cell Formulas
Range	Formula
F2	F2	=SMALL(VALUE(MID(E2,ROW(INDIRECT("1:" &LEN(E2))),1)),1)&SMALL(VALUE(MID(E2,ROW(INDIRECT("1:" &LEN(E2))),1)),2)&SMALL(VALUE(MID(E2,ROW(INDIRECT("1:" &LEN(E2))),1)),3)
H34:H40	H34	=MIN(B34:D34)&MEDIAN(B34:D34)&MAX(B34:D34)