• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

deciog

Active Member
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
 

Attachments

KenU

Active Member
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
 

Attachments

p45cal

Well-Known Member
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.
 
Last edited:

bosco_yip

Excel Ninja
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,"")

79448
 
Last edited:

deciog

Active Member
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
 
Top