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 '19 at 2:39
  • yes... such as 1/10 = 0.1 – nonopolarity Oct 11 '19 at 2:42
  • You need to use split to do that. If you want I can get it for you. – marikamitsos Oct 11 '19 at 2:46
  • or can you create a new column which is to "parse" the column with 1/102 – nonopolarity Oct 11 '19 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 '19 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

| improve this answer | |

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.