SOLVED

Name and Address on the same cell.

Copper Contributor

Hello, I need help please.  I have a list of customer names and address in the same cell.  How do I separate them.  

 

Tom

10 Replies

@Tommentions 

Can you provide a few (anonymized) examples of what the cell values look like?

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

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!

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.

@Tommentions 

 

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?

best response confirmed by mathetes (Silver Contributor)
Solution

@Tommentions 

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.

S1838.png

The address could be split up further, into street, city and stat/zip

@Hans Vogelaar 

 

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. 

Hello Hans, you are brilliant!! Thank you so much for your help. Do you happen to have Vemo?

Tom

@Tommentions 

 

@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.

@Tommentions 

Did you mean Venmo? I don't accept payments - I reply to questions for fun.

Do you want the address to be split too?

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@Tommentions 

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.

S1838.png

The address could be split up further, into street, city and stat/zip

View solution in original post