Read only plain fraction text like 3/4

Turn 3/4 into 0.75, and flag text like 3/0, 1e3/4 or 1/2/3.

What you'll make

In Excel: 3/4 becomes 0.75; 3/0 becomes Review: check the fraction.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(A8,"/")Keeps the top number before the slash. LET calls it n."3"
2TEXTAFTER(A8,"/")Keeps the bottom number after the slash. LET calls it d."4"
3IF(AND( … ),Checks both are plain whole numbers, and the bottom isn't 0.TRUE
4VALUE(n)/VALUE(d)Divides the top number by the bottom number.0.75
5IFERROR(LET( … ))Names both numbers and flags anything else.0.75

Plain means up to six digits each, with no sign, space, leading zero or extra slash.

=IFERROR(LET(n,TEXTBEFORE(A8,"/"),d,TEXTAFTER(A8,"/"),IF(AND(LEN(n)<=6,LEN(d)<=6,TEXT(VALUE(n),"0")=n,TEXT(VALUE(d),"0")=d,VALUE(n)>=0,VALUE(d)>0),VALUE(n)/VALUE(d),"Review: check the fraction")),"Review: check the fraction")

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 fractions. Paste fraction text like 5/8 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

Paste fractions as text. If Excel has already turned 3/4 into a date, it turns amber.

Microsoft's help: TEXTBEFORE function