Get a house number with a fraction
Keeps 1/2, 1/4 or 3/4 with the number. Other words stay with the street.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(TEXTAFTER(A8," ")," ") | The second word of the street line. | "1/2" |
| 2 | IF(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 |
| 3 | TEXTBEFORE(A8," ", … ) | Keeps the words before that space. | "12 1/2" |
| 4 | IFERROR( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste street lines into the green column of the Template tab. The house numbers fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

Return the street after a fraction
12 1/2 Oak St → Oak St
Get the template →
Read the street from a grid address
820 W 400 N → 400
Get the template →
Build a house number like N89W16786
N + 89 + W + 16786 → N89W16786
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →