0

I am trying to sort rows by the number of falts & rooms:

1-1 (flat1 room 1)
11-1 (flat 11 room1)
11-8
12-5
13-1
2
5-5

When sorting these rows in an ascending order, it always give wrong order even if I add 0 before every single digit

https://docs.google.com/spreadsheets/d/1D6q7IsdAIIgD-9GuMNSdLhBVDOtKniDW_cy-Td0Sq_I/edit#gid=1162496797

I tried to make a helper column by adding =arrayformula(text(B1:B,"00")) Also tried to add 0 in front of every number by =arrayformula(0&B5:B). But all didn't work

Improve this question
6
  • Please show what you tried directly in the question body and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén - Volunteer Moderator - ♦
    Commented Feb 27, 2021 at 19:18
  • I got it =iferror(ArrayFormula(IFs(REGEXMATCH(B4:B, "-"), SUBSTITUTE(B4:B,"-",""), B4:B<>0,B4:B*10,B4:B=0,0))) but when I query these data from other sheet , it results in most numbers of zeros!!. see test sheet
    – Hassan Almaateeq
    Commented Feb 27, 2021 at 20:54
  • @HassanAlmaateeq. What exactly is it you are trying to do? And which part is the room number? The 12 part, the 5 part or the 12-5 part. Also please share a greater set of data WITH the expected results.
    – marikamitsos
    Commented Feb 27, 2021 at 21:00
  • Column B is the room number formatted as flat number-room number. Column D is the arrayformula that removed "-" and multiplies single digit with 10 to make the number easily sorted . Then Column F is using query function to brings these data in other tabs but I put it in the same tab for simplicity. I noticed the numbers are not shown all.
    – Hassan Almaateeq
    Commented Feb 27, 2021 at 21:13
  • Please share the expected result and place it in column C
    – marikamitsos
    Commented Feb 27, 2021 at 21:22

1 Answer 1

Reset to default
0

=ArrayFormula(text(ArrayFormula(IFs(REGEXMATCH(O5:O, "-"), SUBSTITUTE(O5:O,"-",""), O5:O<>"-",O5:O*10)),"00000"))

That solved the issue and sorted the rooms perfectly

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.