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

1-1 (flat1 room 1)
11-1 (flat 11 room1)

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


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

  • 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. 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 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. 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. Feb 27, 2021 at 21:13
  • Please share the expected result and place it in column C Feb 27, 2021 at 21:22

1 Answer 1


=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

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.