Google-sheets – How to treat data pasted into Google Sheets as number instead of text and sort accordingly

google sheets

I pasted a table into Google Sheets 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?

Best Answer

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

Related Topic