Sort addresses for data split
 

We're in the process of merging data from one database to a new one. The old
database kept addresses as a single field, i.e., 11700 Princeton Pike. The
new database has two fields, one for Number (11700) and one for street
(Princeton Pike). THe total dataset has over 40,000 records and my test data
set has 1,500. Trying to sort in Excel is not possible, as many posters here
have mentioned. Doing Text-to-Columns just 1,500 times is a big enough pain,
let alone 40,000.
Any suggestions?
--
I know enuff to be dangerous.

Sort addresses for data split
 

You can select your 40000 cell range (single column only) and do one text to
columns. Same with the 1500 version.

And if you wanted to combine them, you could use a formula:

=a1&" "&b1
if a1 contained the number and b1 contained the rest.

Or you could split the combined version based on the first space:

=left(a1,search(" ",a1)-1)
and
=mid(a1,search(" ",a1)+1, 32768)

(32768 can be any number larger than the longest entry)

RealGomer wrote:

We're in the process of merging data from one database to a new one. The old
database kept addresses as a single field, i.e., 11700 Princeton Pike. The
new database has two fields, one for Number (11700) and one for street
(Princeton Pike). THe total dataset has over 40,000 records and my test data
set has 1,500. Trying to sort in Excel is not possible, as many posters here
have mentioned. Doing Text-to-Columns just 1,500 times is a big enough pain,
let alone 40,000.
Any suggestions?
--
I know enuff to be dangerous.

--

Dave Peterson

Sort addresses for data split
 

That would work, but as I said, one field is the street number. And as is
true in most communities, the street numbers can range from a single digit to
5 digits. I did cheat and use the Text to Columns wich got most of the sample
set done. I used a set width with the break being after the third digit. Took
care of most of the cells.
--
I know enuff to be dangerous.


"Dave Peterson" wrote:

You can select your 40000 cell range (single column only) and do one text to
columns. Same with the 1500 version.

And if you wanted to combine them, you could use a formula:

=a1&" "&b1
if a1 contained the number and b1 contained the rest.

Or you could split the combined version based on the first space:

=left(a1,search(" ",a1)-1)
and
=mid(a1,search(" ",a1)+1, 32768)

(32768 can be any number larger than the longest entry)

RealGomer wrote:

We're in the process of merging data from one database to a new one. The old
database kept addresses as a single field, i.e., 11700 Princeton Pike. The
new database has two fields, one for Number (11700) and one for street
(Princeton Pike). THe total dataset has over 40,000 records and my test data
set has 1,500. Trying to sort in Excel is not possible, as many posters here
have mentioned. Doing Text-to-Columns just 1,500 times is a big enough pain,
let alone 40,000.
Any suggestions?
--
I know enuff to be dangerous.

--

Dave Peterson

Sort addresses for data split
 

Maybe the formula approach looking for the first space would have been better.

RealGomer wrote:

That would work, but as I said, one field is the street number. And as is
true in most communities, the street numbers can range from a single digit to
5 digits. I did cheat and use the Text to Columns wich got most of the sample
set done. I used a set width with the break being after the third digit. Took
care of most of the cells.
--
I know enuff to be dangerous.

"Dave Peterson" wrote:

You can select your 40000 cell range (single column only) and do one text to
columns. Same with the 1500 version.

And if you wanted to combine them, you could use a formula:

=a1&" "&b1
if a1 contained the number and b1 contained the rest.

Or you could split the combined version based on the first space:

=left(a1,search(" ",a1)-1)
and
=mid(a1,search(" ",a1)+1, 32768)

(32768 can be any number larger than the longest entry)

RealGomer wrote:

We're in the process of merging data from one database to a new one. The old
database kept addresses as a single field, i.e., 11700 Princeton Pike. The
new database has two fields, one for Number (11700) and one for street
(Princeton Pike). THe total dataset has over 40,000 records and my test data
set has 1,500. Trying to sort in Excel is not possible, as many posters here
have mentioned. Doing Text-to-Columns just 1,500 times is a big enough pain,
let alone 40,000.
Any suggestions?
--
I know enuff to be dangerous.

--

Dave Peterson


--

Dave Peterson