Hi everyone! Can excel do like this?

[apasaja]

HI ALL, CAN EXCEL DO LIKE THIS?

SORT ASCENDING IN BOX.

FROM

​

	A	B	C	D
1	7	5	0	2
2	3	0	8	1
3	5	6	6	2
4	0	0	9	5
5	1	0	0	7

<tbody>
</tbody>

TO

​

E	F	G	H	I
=	0257
=	0138
=	2566
=	0059
=	0017

<tbody>
</tbody>

WHAT IS THE FORMULA?
​

 

[Andrew Poulsom]

Welcome to MrExcel.

Try in F1 copied down:

=MIN(A1:D1)&SMALL(A1:D1,2)&SMALL(A1:D1,3)&MAX(A1:D1)

 

[vinodmalempati]

Try This Formula -

=SMALL(B2:E2,1)&SMALL(B2:E2,2)&SMALL(B2:E2,3)&SMALL(B2:E2,4) Copy down all cells.

 

[apasaja]

it's says #VALUE! sir any suggestion?

 

[apasaja]

ADVERTISEMENT

MrExcel MVP & vinodmalempati

it's says #VALUE! huhuhu

 

[Michael M]

Are they actual numbers in the cells or text ??
Andrews solution works fine for me !

 

[apasaja]

ADVERTISEMENT

format the actual in cell is the number..

 

[Michael M]

Ok, have a look at my tag and download the HTML Maker to post a sample of the data and the associated formulae, back here.

 

[Useful]

Hi everyone!

like this? (array formula with CSE)
=TEXT(SUM(SMALL(A1:D1,{1;2;3;4})*{1000;100;10;1}),"0000")