Sorting pre 1752 years for genealogy purposes

[jacm]

I am trying to sort by years (I have 66230 records to sort on) Parish Records that date from 1538/1659. The year as we know it now - did not come into existence until 1752 - and i have been trying to sort my data on the "years". I know i have done this before - however, i have either forgotten how to do it - or the Excel 2010 was not the version i accomplished this on.

Basically i have this:-

25 Sep 1596/97
25 Jun 1596
05 May 1611
10 Feb 1611/12

When i do a normal "sort" the split year comes before the main year as in 1596/97 comes before 1596 - thus putting all the dates out of sequence.

Can anyone suggest how to over come this please - i am no good with macro's - so if it can be done with using sort - it would be easier for me.

Thank you for your time and energy

JM

 

[pgc01]

Hi

I'm assuming you have the year in a separate column.

Can it be that you have the 1596 as a number?
1596/97 is stored as a text value, because of the slash. This means that if you want to sort correctly all the values must be text values.

 

[jacm]

Hi

I'm assuming you have the year in a separate column.

Can it be that you have the 1596 as a number?
1596/97 is stored as a text value, because of the slash. This means that if you want to sort correctly all the values must be text values.

============================

Hello, firstly, thank you very much for such a quick reply.

I have two versions of my page - one with years in two separate columns and one page with the years in one column. I did have GENERAL as my default - changed it to TEXT - unfortunately, the result is still the same.

Will try again today to get it to work.

But, thanks again for your help

JM

 

[pgc01]

Hi again

Did you change the format to text before or after you wrote the numbers?

If you have a number value in a cell, changing its format to text doesn't change the value type.

The value types must be text, not the format of the cell.

To test, for ex., if the value in A1 is text, write in another cell:

=ISTEXT(A1)

Please test some of your values to check if they are text.

 

[jacm]

Hi again

Did you change the format to text before or after you wrote the numbers?

If you have a number value in a cell, changing its format to text doesn't change the value type.

The value types must be text, not the format of the cell.

To test, for ex., if the value in A1 is text, write in another cell:

=ISTEXT(A1)

Please test some of your values to check if they are text.

=============

Unfortunately, my knowledge on how to Test using your =ISTEXT(A1) did not work - HOWEVER, i did insert another column and made sure TEXT was showing in the Format Cells column - then proceeded to type in a dozen dates - then sorted them - and it WORKED.

I did not realise you could not 'change horses' mid race and i also thought GENERAL and TEXT would have been one and the same - however, that too proved wrong.

The only thing now is - How in the world do i change over 66thousand entries - i am hoping i can copy and paste into a pre column set to TEXT.

So yet again - thank you for your quick reply - and hopefully in five minutes time - i will be onto something else.

JM

 

[pgc01]

I'm glad your test worked ok.

This is a quick way to change all values in a column to text

1 - select the column
2 - invoke Text-to-Columns
3 - in the first panel choose delimited and press Next
4 - in the second panel clear all the delimiters boxes (if they are not already cleared) and press Next
5 - in the third panel chek the Text radio button and press Finish

This changes all the values in the column to text type.

Please test.

 

[pgc01]

Another way, using formulas.

For ex. if the values are in column A

1 - use in B1: =TEXT(A1,"@")
2 - copy down till the end
3 - select column B, Copy, Paste Special Values

Now in B you have a copy of the values in A but all type text.

 

[jacm]

Gosh your replies are so quick - either you are up very late - or you live in Australia.

But, this email is sent with my great thanks - all is well - and i have to admit - i could see the visual change that took place using Text to Columns - a very valuable lesson has been learnt - and for that i really Thank You

JM

 

[pgc01]

You're welcome. Thanks for the feedback.

either you are up very late - or you live in Australia.

No, i'm in GMT ... and you're right, time to go to bed!