Read mixed fractions like -1 1/2
Turn -1 1/2 typed as text into the number -1.5, keeping the minus sign.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | t,IF(LEFT(A8,1)="-",MID(A8,2,99),A8) | Takes off a leading minus sign, called t. | "1 1/2" |
| 2 | whole,TEXTBEFORE(t," "),frac,TEXTAFTER(t," ") | Splits t at the space into whole and fraction. | "1" and "1/2" |
| 3 | IF(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 |
| 5 | LET( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own list. Paste fractions like -2 3/4 into the green column of the Template tab. The numbers fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

Combine a whole number and a fraction
1 and 1/2 → 1.5
Get the template →
Turn fraction text into a number
1/2 as text → 0.5
Get the template →
Build a fractional house-number label
12 and 0.5 → 12 1/2
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →