Get a house number with a fraction

Keeps 1/2, 1/4 or 3/4 with the number. Other words stay with the street.

What you'll make

In Excel: 12 1/2 Oak St becomes 12 1/2.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(TEXTAFTER(A8," ")," ")The second word of the street line."1/2"
2IF(OR( … ="1/2", … ="1/4", … ="3/4"),2,1)Gives 2 when that word is 1/2, 1/4 or 3/4, else 1.2
3TEXTBEFORE(A8," ", … )Keeps the words before that space."12 1/2"
4IFERROR( … )Says Review when the line has too few words."12 1/2"

Part 1 is used three times, once per fraction. Parts 2 and 3 work from the inside out.

=IFERROR(TEXTBEFORE(A8," ",IF(OR(TEXTBEFORE(TEXTAFTER(A8," ")," ")="1/2",TEXTBEFORE(TEXTAFTER(A8," ")," ")="1/4",TEXTBEFORE(TEXTAFTER(A8," ")," ")="3/4"),2,1)),"Review street line")

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 addresses. Paste street lines into the green column of the Template tab. The house numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only 1/2, 1/4 and 3/4 stay with the number. Check any other fraction by hand.

Microsoft's help: TEXTBEFORE function