1

I pasted a table into Google Sheets (part of Google Docs), and one column is

1/234
1/102
1/1222
1/83

and when sorted according to this column, the column data is treated as "text" and sorted accordingly, meaning 1/102 and 1/1222 both go to the front of the table. Is it possible to sort them as numbers?

  • By numbers do you mean keep them as fractions? – marikamitsos Oct 11 at 2:39
  • yes... such as 1/10 = 0.1 – nopole Oct 11 at 2:42
  • You need to use split to do that. If you want I can get it for you. – marikamitsos Oct 11 at 2:46
  • or can you create a new column which is to "parse" the column with 1/102 – nopole Oct 11 at 2:59
  • It depends on your needs. One way is to use =ArrayFormula(SPLIT(A2:A7,"/",)) to split and then drag down =B2/C2 – marikamitsos Oct 11 at 3:07
0

Add a new column then try formula this :

=REGEXEXTRACT(A1,"(.*)\/")/REGEXEXTRACT(A1,"\/(.*)")

Or with array formula :

=arrayformula(REGEXEXTRACT(A1:a,"(.*)\/")/REGEXEXTRACT(A1:a,"\/(.*)"))

Then sort/filter the data with this new column.


To revert the process you can try this quick formula to force the custom format :

=TEXT(B1,"0/?????")

Illustration :
enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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