Read mixed fractions like -1 1/2

Turn -1 1/2 typed as text into the number -1.5, keeping the minus sign.

What you'll make

In Excel: -1 1/2 as text becomes -1.5.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1t,IF(LEFT(A8,1)="-",MID(A8,2,99),A8)Takes off a leading minus sign, called t."1 1/2"
2whole,TEXTBEFORE(t," "),frac,TEXTAFTER(t," ")Splits t at the space into whole and fraction."1" and "1/2"
3IF(LEFT(A8,1)="-",-1,1)Gives -1 if the cell started with a minus.-1
4(whole+TEXTBEFORE(frac,"/")/TEXTAFTER(frac,"/"))Adds the whole number and the top over bottom.1.5
5LET( … )Multiplies the sign by the amount.-1.5

The minus applies to the whole value, so -0 1/2 gives -0.5.

=LET(t,IF(LEFT(A8,1)="-",MID(A8,2,99),A8),whole,TEXTBEFORE(t," "),frac,TEXTAFTER(t," "),IF(LEFT(A8,1)="-",-1,1)*(whole+TEXTBEFORE(frac,"/")/TEXTAFTER(frac,"/")))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own list. Paste fractions like -2 3/4 into the green column of the Template tab. The numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Write a whole number even when it's zero, like -0 1/2. Keep the cells as text, or Excel may change them.

Microsoft's help: LET function