Hello,

I'm having some difficulty in comparing the two lists below. Both sets of
data come from different databases, the first an AS/400, the second a SQL
database.

sort code Custom
20/11/1981 201181
93-82-70 938270
95-03-66 950366
07/01/2016 070116

The AS/400 mainly stored the 6 digit code as XX-XX-XX, but sometimes it was
reformatted as a date field - hence 20-11-81 became 20/11/1981
How can I convert these to a 6 digit string sop that I can compare thousands
of records against our new custom database?
I have tried Text-to-Columns, forcing General, but the date fields come in
screwy, and the - fromatting is preserved!

Help!!
charlotte

Hi Charlotte

Take your data from the AS400 and covert it first. With the data in
column A, enter in B1
=TEXT(A1,"dd-mm-yy")
and copy down as far as required.
Copy this column and Paste SpecialValues to "fix" the data.
Now you will have data you can compare with your other list.

--
Regards

Roger Govier


"Charlotte Howard" wrote in
message ...
Hello,

I'm having some difficulty in comparing the two lists below. Both
sets of
data come from different databases, the first an AS/400, the second a
SQL
database.

sort code Custom
20/11/1981 201181
93-82-70 938270
95-03-66 950366
07/01/2016 070116

The AS/400 mainly stored the 6 digit code as XX-XX-XX, but sometimes
it was
reformatted as a date field - hence 20-11-81 became 20/11/1981
How can I convert these to a 6 digit string sop that I can compare
thousands
of records against our new custom database?
I have tried Text-to-Columns, forcing General, but the date fields
come in
screwy, and the - fromatting is preserved!

Help!!
charlotte

Thanks Roger, that did the trick!

Charlotte

"Roger Govier" wrote:

Hi Charlotte

Take your data from the AS400 and covert it first. With the data in
column A, enter in B1
=TEXT(A1,"dd-mm-yy")
and copy down as far as required.
Copy this column and Paste SpecialValues to "fix" the data.
Now you will have data you can compare with your other list.

--
Regards

Roger Govier


"Charlotte Howard" wrote in
message ...
Hello,

I'm having some difficulty in comparing the two lists below. Both
sets of
data come from different databases, the first an AS/400, the second a
SQL
database.

sort code Custom
20/11/1981 201181
93-82-70 938270
95-03-66 950366
07/01/2016 070116

The AS/400 mainly stored the 6 digit code as XX-XX-XX, but sometimes
it was
reformatted as a date field - hence 20-11-81 became 20/11/1981
How can I convert these to a 6 digit string sop that I can compare
thousands
of records against our new custom database?
I have tried Text-to-Columns, forcing General, but the date fields
come in
screwy, and the - fromatting is preserved!

Help!!
charlotte