jeffcoleky
Active Member
- Joined
- May 24, 2011
- Messages
- 274
I'm trying to remove the suffix in some addresses so that it only reads the first part of the street address.
The catch is that sometimes there is a ZIPCODE in the same field and sometimes there is not. Also, sometimes it reads "Ave, Rd. or St." and sometimes "AVENUE, ROAD, STREET".
The wording of the formula might be, "remove the last word from the cell up to the first space unless there is a number also in which case remove the last TWO"
I used this code but if there is more than one word in the address it doesn't extract them both: =LEFT($A2,FIND(" ",$A2,8)-1)
Here's the sample data:
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
</tbody>
Is there an easy way to do this?
The catch is that sometimes there is a ZIPCODE in the same field and sometimes there is not. Also, sometimes it reads "Ave, Rd. or St." and sometimes "AVENUE, ROAD, STREET".
The wording of the formula might be, "remove the last word from the cell up to the first space unless there is a number also in which case remove the last TWO"
I used this code but if there is more than one word in the address it doesn't extract them both: =LEFT($A2,FIND(" ",$A2,8)-1)
Here's the sample data:
Excel 2010
A | B | |
---|---|---|
Before | Desired Output | |
1585 SOUTH 15TH STREET 58928 | 1585 South 15th | |
1358 DIXDALE AVENUE 58218 | 1358 Dixdale | |
5815 LAMOND DRIVE | 5815 Lamond | |
5325 HAVENTREE PLACE | 5325 Haventree | |
5383 WOLF PEN TRACE 58859 | 5383 Wolf Pen | |
5929 BAY HARBOR DRIVE | 5929 Bay Harbor | |
2527 WEST BURNETT AVENUE | 2527 West Burnett | |
7985 PICKWICK LANE | 7985 Pickwick | |
2815 FARNSLEY ROAD 58213 | 2815 Farnsley | |
2131 SADIEVILLE DRIVE 58217 | 2131 Sadieville | |
5788 VARBLE AVENUE 58211 | 5788 Varble | |
7218 PEPPERMILL DRIVE 58228 | 7218 Peppermill |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
</tbody>
Sheet1
Is there an easy way to do this?