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
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?
HansVogelaar 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.
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.
