Function to remove duplicate numbers in a single cell sorting ascending order

[deciog]

Gentlemen, good morning.

I can't solve it, I ask for your help, explanation in the model

Remembering I need formula for version of Excel 2016 and also for version 365

Not VBA and not Power BI

thanks in advance

Decio

 

[KenU]

Here is a possible solution that works for either case. I am assuming that you are trying to avoid helper cell concatenation, but will allow the use of CONCAT within a formula. If that is the case the formula below should work.

=CONCAT(IF(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9},CONCAT(B22:B26))),{1,2,3,4,5,6,7,8,9},""))

The bold red would reference the input cell or range. The revised workbook is attached.

Hope that helps.

Regards,
Ken

 

[p45cal]

Excel 2016 has CONCAT so (you may need to Ctrl+Shift+Enter this to ommit it to the sheet):
=CONCAT(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},CONCAT(B11))),{0,1,2,3,4,5,6,7,8,9},""))
or for a range (it's the same):
=CONCAT(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},CONCAT(B22:B26))),{0,1,2,3,4,5,6,7,8,9},""))
edit post posting: Ha, beaten to it!
Note also the inclusion of zero in mine.

 

[bosco_yip]

Normal Excel 2016 do not have CONCAT function unless you have Excel 2016 Professional Edition

Maybe try this formula for all old Excel versions

1] H11, array formula ("Ctrl+Shift+Enter")

=SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW(1:9),B11)),ROW(1:9))*10^(9-ROW(1:9))),0,"")

2] H22, array formula ("Ctrl+Shift+Enter")

=SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW(1:9),B22&B23&B24&B25&B26)),ROW(1:9))*10^(9-ROW(1:9))),0,"")

 

[deciog]

Ken U, Good morning. Worked, thank you.

p45cal, Good Morning It worked thank you.

bosco_yip, Good Morning, You are correct some versions do not have this function, your solution also worked thank you very much.

Decio

 

[p45cal]

Did you need to see zeroes too?

 

[deciog]

p45cal, I don't need thanks

Decio