Sorting pre 1752 years for genealogy purposes

jacm

New Member
Joined
Feb 24, 2012
Messages
11
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

New Member
Joined
Feb 24, 2012
Messages
11
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

New Member
Joined
Feb 24, 2012
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

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

New Member
Joined
Feb 24, 2012
Messages
11
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top