Import Data

[Myrkk]

I'm Back!!! Last time I was here I needed help with sorting data. The data was

From:
---A
1 Mr. Smith
2 123 Fourth St
3 San Jose CA 95111
4 Mr. Right
5 456 Down Town
6 San Jose CA 95112

To:

1 A----------------B----------------------C----------------D-----------E
2 Mr. Smith----123 Fourth S------San Jose-----CA--------95111
3 Mr. Right-----456 Down Town--San Jose----CA--------95112
I used --- to separate cells.

Using this formula
In cell B1 enter the formula: =A2
In cell C1 enter the formula: =LEFT($A3,LEN($A3)-9)
In cell D1 enter the formula: =MID($A3,LEN($A3)-7,2)
In cell E1 enter the formula: =RIGHT($A3,5)

Now I am back with a 4 line address vs 3.
Star Corp
Mr Smith
123 Forth St
San Jose CA 95111

How do I get that to sorted out?

[Ron Coderre]

With data structured like this:
Company
Name
Street
CityStateZip

Samples for A1:A12....
Alpha Corp
Mr. Smith
123 Fourth St
San Jose CA 95111
Star Corp
Mr Banas
345 Main street
San Diego CA 95111
Charlie Company
David Smith
999 Any Way
Newville, MD 12345

This formula begins listing the values this way:
Company---Name---Street---CityStateZip

Please Login or Register  to view this content.

Copy that formula across through E1 and down as far as you need.

With the sample data, these are the results for B1:E3

Please Login or Register  to view this content.

Is that something you can work with?

[Myrkk]

That works really well. Thank you so very much

With data structured like this:
Company
Name
Street
CityStateZip

Samples for A1:A12....
Alpha Corp
Mr. Smith
123 Fourth St
San Jose CA 95111
Star Corp
Mr Banas
345 Main street
San Diego CA 95111
Charlie Company
David Smith
999 Any Way
Newville, MD 12345

This formula begins listing the values this way:
Company---Name---Street---CityStateZip

Please Login or Register  to view this content.

Copy that formula across through E1 and down as far as you need.

With the sample data, these are the results for B1:E3

Please Login or Register  to view this content.

Is that something you can work with?