0

I have a list of files' names that I would like to sort based on the date that's impeded within the file name text (Column A folder). When sorting them, the number 10 comes after 1 instead of 1,2,3,4,5....etc. So, I came up with a new column (G) that has the number of day only being extracted from the file name (Column A) by this formula:

=ArrayFormula(iferror(Right(REGEXEXTRACT(A2:A,"/.."),2)))

But still I can't sort the rows as expected... 10 comes after 1, 20 after 2 and 30 after 3.. Any idea how to solve this issue?enter image description here

Improve this question
4
  • 1
    What is the content of column A? We cannot see all of it in the image. Also. Is it formatted as text, as number or what. As always, none of these questions would exist if you could share a test sheet. Since you create quite a few questions, create a sheet for all of them and a new tab for each question. Just a thought.
    – marikamitsos
    Feb 18, 2021 at 12:15
  • Column A is formatted as TEXT.
    – Hassan Almaateeq
    Feb 19, 2021 at 7:04
  • 1
    I think that Erik has already answered your question. Since you want to sort the column as well, you could use =ArrayFormula(SORT(TEXT(VALUE(REGEXEXTRACT(FILTER(A2:A,A2:A<>""),"/(\d+)")),"00"),1,1)) If still in trouble, let us know.
    – marikamitsos
    Feb 19, 2021 at 13:27
  • Thanks. I would like to sort the range A2:E by E. Column E has this arrayformula. While your answer will sort the isolated column
    – Hassan Almaateeq
    Feb 19, 2021 at 13:45

2 Answers 2

Reset to default
1

What you actually want is to sort your data based on column A.
To do this you do not need a helper column (like column E).

This is the simpler formula you need to use

=SORT(A2:D,ArrayFormula(IFERROR(TEXT(VALUE(REGEXEXTRACT(A2:A,"/(\d+)")),"00"))),1)

enter image description here

Improve this answer
1

Try this in place of your formula:

=ArrayFormula(TEXT(VALUE(REGEXEXTRACT(FILTER(A2:A,A2:A<>""),"/(\d+)")),"00"))

By converting the values of the REGEXTRACT portions to text in the format "00", you'll have leading zeros in front of otherwise single digits and should be able to sort normally.

Improve this answer
4
  • That's great.. Thanks alot
    – Hassan Almaateeq
    Feb 19, 2021 at 7:03
  • Hassan, you should always post all of the details of an issue in your posts. Every detail matters. And developing a solution without all the details only leads to delay for you and potential frustration for people trying to help you. Please also be sure that your links are set to "Anyone with the link..." and "Editor," not just "View Only." Otherwise, no one can make changes to the sheet. I do not have time right now to look into fixing this, but I will try to take a look over the weekend.
    – Erik Tyler
    Feb 19, 2021 at 23:33
  • I just looked at your sheet, and it is literally blank. If you want help, please share a link to a sheet that illustrates the problem you are having.
    – Erik Tyler
    Feb 19, 2021 at 23:35
  • I do not understand your additional comment. I also looked at your test sheet again, and I have no idea from looking at it what you are trying to do. I don't see the "one extra question" anywhere. Hopefully, @marikamitsos was able to understand what you needed and provide what you needed in his reply.
    – Erik Tyler
    Feb 21, 2021 at 4:52

Your Answer

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

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