Forum Discussion
Solved
Name and Address on the same cell.
Hello, I need help please. I have a list of customer names and address in the same cell. How do I separate them. Tom
I'm in Europe, it was past midnight for me...
Perhaps this? Let's say you have the combined names/addresses in A2 and down.
In B2:
=LEFT(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)-2)
In C2:
=RIGHT(A2,LEN(A2)-MATCH(TRUE,ISNUMBER(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)+1)
If you don't have Microsoft 365 or Office 2021, confirm both formulas by pressing Ctrl+Shift+Enter.
Then fill down.
The address could be split up further, into street, city and stat/zip
Here are a couple of examples.
Top Brothers Inc 2059 S. Larry Street, Upland, Ca 98211
Chips Inc 2089 N H Street, Chino, Ca 91744
Top Brothers Inc 2059 S. Larry Street, Upland, Ca 98211
Chips Inc 2089 N H Street, Chino, Ca 91744
Hi Hans, anything you can do to help me with this. My listing is so large and I really need to separate customer name form address.
Thank you!
Thank you!
I'm in Europe, it was past midnight for me...
Perhaps this? Let's say you have the combined names/addresses in A2 and down.
In B2:
=LEFT(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)-2)
In C2:
=RIGHT(A2,LEN(A2)-MATCH(TRUE,ISNUMBER(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)+1)
If you don't have Microsoft 365 or Office 2021, confirm both formulas by pressing Ctrl+Shift+Enter.
Then fill down.
The address could be split up further, into street, city and stat/zip
- Hello Hans, you are brilliant!! Thank you so much for your help. Do you happen to have Vemo?
TomDid you mean Venmo? I don't accept payments - I reply to questions for fun.
Do you want the address to be split too?
Good job, Hans, assuming that the addresses always begins with a number. One does need to make an assumption of a regular pattern, something along those lines. It'll be interesting to see how Tommentions responds.
It would help if there are some discernible patterns. In the two examples you give--more would be helpful--
- both customer names end with "Inc."
- both have the word "Street" at the end of the street address
- both have only 5 digit zip codes at the end
Do ALL of the addresses follow those patterns? If so, regular patterns like that can be used to accomplish the separation you're seeking. But if they don't, unless all the ones that don't have "Inc." have, say, "LLC" or at most one or two other standard designations, it is not going to be straight forward. The less "standardized" your addresses are, the less likely a nice neat separation can be achieved.
And just for the record, you'd be best served in the long run if you separate the address into
- Street 1
- Street 2
- City
- State
- Zip
Similarly, unless all of the customer names are, in fact, COMPANY names (as in your two examples), you'd be best served by separating first from last names of any individual contacts.
A must ask question: is it possible to go back to whatever the source was here and get the data pre-separated? Or is this how, say, your predecessor kept the data?
