I have an address field that needs to be split in 2. Now the field contains the house number street comma suite # (ex: 21 Main Street, Suite 25). I would like to separate it, by the comma, so the adress1 would be 21 Main Street and Adress 2 would be suite 21.
I think I need 2 formulas:
1. So far I have =LEFT(F2,SEARCH(",",F2,1 )-1 ), so address 1 shows the house number and street, without the comma. The problem is that there are some addresses without suites and without commas. I get an error in those rows. In this case, I need the formula to just copy the contents of the original cell.
2. I need a formuls to get the Suite # out of the original cell and into the cell labeled address2.
Thanks
Is there a reason you can't use the text to columns feature, or is this something you're unaware of? Seems like a much simpler solution to your problem.
Try =LEFT(F2,SEARCH(",",F2&",",1 )-1 )1. So far I have =LEFT(F2,SEARCH(",",F2,1 )-1 ), so address 1 shows the house number and street, without the comma. The problem is that there are some addresses without suites and without commas. I get an error in those rows. In this case, I need the formula to just copy the contents of the original cell.
=MID(F2,SEARCH(",",F2&",")+2,30) I used "+2" because in your example, there is a space between your comma and your suite.2. I need a formuls to get the Suite # out of the original cell and into the cell labeled address2.
Does this work for you?
PS. Broken Biscuits' idea is probably better.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
FORGET THIS QUESTION-I found a built in wizard to do it
That'll be the text to columns feature then?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks