address segment splitting excel

I have received an extract of data that should represent an address in a single column of data. This should be split into address1, 2, 3, 4 - but for some reason the only export facility puts all the address segments into a single cell, e.g

123 ee street                     city                  state
 
there is no consistency between the number of blank spaces per address 'segment'. Can anyone think of a way of splitting the segments into separate cells? I cant even use spaces in text to columns as each segment can contain spaces, e.g. "123 ee street" is one segment but using spaces would split "123", "ee", "street".

bit of a nightmare task so any ideas most welcome. Its currently an excel spreadsheet but I suppose I could convert to something else if that will make the segregation better/easier.
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
How about doing a Find and Replace to replace double space "[space][space]" with another character eg # or |. Then use ttc on that character. Where there is an odd number of spaces in a multiple string the replace will start at the beginning so will end up with a space on the end of a string of # characters. Text to column will then have that as the first character of the next column so you can then use TRIM to remove leading spaces. Your example above has an odd number of spaces between street and city so becomes (separated in lines to demonstrate cells):

123 ee street
[space]city
state

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Can you attach a sample of the data?
tnayakCommented:
he attached workbook uses a combination of the LEFT, FIND, SUBSTITUTE & LEN functions to separate the text into columns.  You may have to verify that gaps between text are spaces (and some other non-printing character).
Remove-Extra-Spaces.xlsx
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pma111Author Commented:
>and some other non-printing character

How could we check that?

Thanks
Rob HensonFinance AnalystCommented:
Copy one of the "spaces" into an empty cell. Then use formula:

=CODE (cell)

If it is a standard space result will be 32
pma111Author Commented:
yes they were 32's when I checked with the code function.
pma111Author Commented:
@rob henson - I tried this - How about doing a Find and Replace to replace double space "[space][space]" with another character eg # or |.

But the text to columns put the 2nd 'segment' however many columns away, so if after the search and replace (replacing double spaces with #) there are 10 #'s within the 'gap',, the text to column based on # then puts the next segment 10 columns away, whereas I just need it in the next column, e.g. column B!
pma111Author Commented:
actually I did what you said and then just kept search and replace on ## for # and then when I got it down to 1 just ttc on that..
Rob HensonFinance AnalystCommented:
In the ttc there is an option to treat multiple delimiters as one, upper right section in step where choosing the delimiter.
pma111Author Commented:
Thanks Rob
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.