How do I convert a column of strings like 1/2 and 2/3 into decimal numbers like 0.5 and 0.666?

For example, can I force the entire column to be evaluated as formulas, without needing to prepend = in each cell individually?


Option 1

Convert "fractions" into numbers in another column

You can convert text strings like 1/2 and 2/3 in column A2:A into decimal numbers like 0.5 and 0.666 like this:

    regexextract(trim(A2:A), "^(\d+)") 
    regexextract(trim(A2:A), "(\d+)$") 

Place the formula in row 2 of a free column. The new column will contains the converted values, and the source text strings will remain unchanged.

Option 2

Unpack the numerators and denominators in "fractions" into their own columns

To split the numerators into one column, and denominators into another column, use this formula in row 2 of a free column with another free column available immediately in the right:

=arrayformula( iferror( split(trim(A2:A), "/", false, true) ) )

To calculate the quotients of the numerators and denominators, use a formula like =arrayformula( iferror(I2:I / J2:J) ) where I2:J are the new columns.

Option 3

Convert "fractions" into formulas in place

In the general case, there is not much point to try and implement a parser to evaluate a text string as a spreadsheet formula. It is much easier to use the initial = and let Google Sheets do the hard lifting.

To permanently convert text strings like 1/2 and 2/3 into formulas like =1/2 and =2/3 that return decimal numbers like 0.5 and 0.666, replacing the values in place, use Edit > Find and replace:

Find: ^(\d+/\d+)$
Replace with: =$1
Search: This sheet
☑️ Match case
☑️ Search using regular expressions
Replace all

  • Is there no way to unpack function arguments from an array, as in =DIVIDE(*SPLIT(A1, "/"))?
    – benjimin
    Jun 7 at 5:16
  • Manually adding the = to each successive cell is cumbersome if a long table of data (students scores) has already been tabulated in this number format (i.e. as fractions).
    – benjimin
    Jun 7 at 5:18
  • The last option is by far the best IMHO
    – testing_22
    Sep 27 at 22:50

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.