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.

 

[Fluff]

The problem is that you numbers in col E are not all 3 digit numbers.
Did you try my suggestion, that caters for that?

 

[mft38Excel]

This XL2BB did not capture as I intended. Text boxes caused column F to over extend and Text boxes to disappear.

 

[Fluff]

The textboxes as you call them are still there, you just need to scroll over to see them.
But does not change the reason why the formula is failing.

 

[mft38Excel]

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")

I copied original table and changed my "straight" field [E54] from numeric to text so the formula could reference it but it gives no results. What am I missing?

3-Digits-Straights-convert-to-Boxed.xlsx
	A	B	C	D	E	F	G
53	seq_no	strai_1	strai_2	strai_3	straight		boxed
54	0001	4	1	0	410	#######	014
55	0002	0	0	0	0		000
56	0003	0	1	5	15
57	0004	0	2	4	24
58	0005	0	3	3	33
59	0006	0	4	2	42
60	0007	0	5	1	51		015
61	0008	0	6	0	60
62	0009	1	0	5	105
63	0010	1	5	0	150
64	0011	3	0	3	303
65	0012	3	2	1	321
66	0013	3	3	0	330
67	0014	5	0	1	501
68	0015	5	1	0	510
3 straights to 3 boxed

Cell Formulas
Range	Formula
F54	F54	=TEXT(SUM(10^{2,1,0}*AGGREGATE(15,6,MID(TEXT(E54,"000"),{1,2,3},1)+0,{1,2,3})),"000")

 

[Fluff]

You may need to confirm the formula with Ctrl Shift Enter rather than just Enter.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Convert 3-digit straight lotto number to 3-digit boxed lotto number [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.