Oct 06 2022 02:52 PM
Hello, I need help please. I have a list of customer names and address in the same cell. How do I separate them.
Tom
Oct 06 2022 02:58 PM
Can you provide a few (anonymized) examples of what the cell values look like?
Oct 06 2022 03:03 PM
Oct 06 2022 03:53 PM
Oct 06 2022 04:48 PM - edited Oct 06 2022 04:51 PM
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.
Oct 06 2022 05:42 PM
It would help if there are some discernible patterns. In the two examples you give--more would be helpful--
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
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?
Oct 07 2022 01:53 AM
SolutionI'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
Oct 07 2022 07:08 AM - edited Oct 07 2022 07:10 AM
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.
Oct 07 2022 07:10 AM
Oct 07 2022 07:41 AM
@Hans Vogelaar is brilliant, absolutely.
I do want to emphasize that, depending on how you plan to use this now "cleaner" database, you still may want to break the address apart as I've suggested above. If, for example, you plan to create address labels for mailings, it would be far easier to use Mail Merge (in MS Word), drawing from the Excel data, if those data elements were separate. Even if that is not your plan, doing such things as sorting by city (if you have sales reps calling on customers)....that would be facilitated by having City as its own piece of information.
Oct 07 2022 09:18 AM
Did you mean Venmo? I don't accept payments - I reply to questions for fun.
Do you want the address to be split too?
Oct 07 2022 01:53 AM
SolutionI'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