Forum Discussion
Name and Address on the same cell.
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
Top Brothers Inc 2059 S. Larry Street, Upland, Ca 98211
Chips Inc 2089 N H Street, Chino, Ca 91744
If all the names & addresses are in Column A.
Cell B1, To find the first number(Assuming there are no numbers in the Customer names and the address begins with a letter). I used this formula: =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1
It basically looks for the first character that is a number and goes back 1 to let you know the position.
Cell C1: =LEFT(A1,B1) That would give you the text left of the first number
Cell D1: =RIGHT(A1,LEN(A1)-B1) Takes the total characters and minus the left number of characters.- 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!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?
Tom
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?
