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 91744If 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
Can you provide a few (anonymized) examples of what the cell values look like?
