Comparing Data in different formats

[Guest]

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

 

[Roger Govier]

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 Special>Values to "fix" the data.
Now you will have data you can compare with your other list.

 

[Guest]

Thanks Roger, that did the trick!

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 Special>Values to "fix" the data.
Now you will have data you can compare with your other list.