Deconcatenate

[Michael]

Hello,
I need some help ASAP for any of you that can save me!

In Excel '07, I have a cell with all of a persons contact info.

Firstname MI Lastname Address Street State Zip

Problem is there is no uniform structure. No tabbing, identifiers, and
everything might not be included. For example:

Jane M Doe 123456789 2nd Street Dallas
Jane Doe 1 Main Street Dallas, TX 65654-4343
Jack Smith 234 W. Broadway Dallas, Texas 34212

Any idea how I can deconcatenate this without spending hours and hours?
Thanks for the help in advance.

 

[Dave]

Hi,
I would begin by splitting the data at the first digit, which will separate
the name and the address.
=LEFT(A2,FIND({1,2,3,4,5,6,7,8,9,0},A2,1)-1) gives the name
=MID(A2,FIND({1,2,3,4,5,6,7,8,9,0},A2),100) gives the address
Put these in adjacent cells and copy down.

The above works as long as the address begins with a number.
You may need to manually alter addresses that don't conform.

How many columns do you want to use?

Regards - Dave.

 

[Dave]

Oh, and it will also fault if a name contains a digit -
like Bartholomew Isaiah Darlington the 3rd.
But surely no parent would be so cruel...
Dave.

 

[Michael]

Okay I'm inching my way there.

Is there a way of saying give me all the stuff in between two things (I know
the two ends)e.g.

I want "windows internet explorer"

from

hello hello windows internet explorer bye
hello windows internet explorer bye bye
hello 1 2 hello 1 windowns internet explorer 1 2 3

when I have cells that have:
hello hello AND bye
hello AND bye bye
hellow 1 2 hello 1 AND 1 2 3

Thanks, I hope you understand.

 

[Ron Rosenfeld]

Oh, and it will also fault if a name contains a digit -
like Bartholomew Isaiah Darlington the 3rd.
But surely no parent would be so cruel...
Dave.

It will also fault if the street address has no number.
--ron

 

[Dave]

Um, no, don't really understand. Give a couple examples of what you are
actually working with, and the results you want.
Dave.

 

[Michael]

well i approached these cells from both sides, so now i have the information
from left and right, but not in the middle. For example if I had something
like this:

John J. Doe 123 West Main Street Suite 100 Dallas Texas 64852

So, all of these in individual cells
John
J.
Doe

Dallas
Texas
64852

Is there a way of saying: Get all the text in the concatenated cell between
"Doe" and "Dallas" and put it in a cell for home address.

Thanks!

 

[Dave]

Hi Michael,
I must be slow today. Still not sure what you actually have now, and what
you actually want.
You started with:
John J. Doe 123 West Main Street Suite 100 Dallas Texas 64852
But after doing some work, you now have:
John
J.
Doe

Dallas
Texas
64852
Is that right? Are they in cells going down? eg E2:E7?
With the original address in A2, put this somewhere:
=MID(A2,FIND(E4,A2)+LEN(E4)+1,(FIND(E6,A2))-(FIND(E4,A2)+LEN(E4)+1))
Any good?
Dave.