On Fri, 23 Nov 2007 21:45:57 +1300, "Himself" <himself@himself.com> wrote in soc.genealogy.computing: >I am using Excel 2000 for a one name study project. > >Currently there are 3100 rows and 22 cols. > >Apart from one column all others sort in order. > >The column that will not sort correctly is the one with the forenames in it. > >Currently I get, for example: > >John >John >John >John Albert >John Frederick >John George >John William >John >John >Jonathan Put the cursor over the last John and check (with F2) if it is "John ", i.e. with a space at the end. Or look for "* " using "full cell" (not sure about the option in English since I use the French version of Excel). >I have formatted all cells to Text >Justified the text to the left > >but still it will not sort in strict alpha order. > >The same file on other PCs using Excel 2003 and Open Office displays the >same problem, thus it is my file that is the issue. Denis -- 0 Denis Beauregard - /\/ Les Français d'Amérique du Nord - www.francogene.com/genealogie--quebec/ |\ French in North America before 1722 - www.francogene.com/quebec--genealogy/ / | Maintenant sur cédérom, début à 1770 (Version 2008) oo oo Now on CD-ROM, beginnings to 1770 (2008 Release)

"Denis Beauregard" <denis.b-at-francogene.com@fr.invalid> wrote in message news:qosdk3dha6qlns6v1thn2ajuessm1du6io@4ax.com... > On Fri, 23 Nov 2007 21:45:57 +1300, "Himself" <himself@himself.com> > wrote in soc.genealogy.computing: > >>I am using Excel 2000 for a one name study project. >> >>Currently there are 3100 rows and 22 cols. >> >>Apart from one column all others sort in order. >> >>The column that will not sort correctly is the one with the forenames in >>it. >> > > Put the cursor over the last John and check (with F2) if it is > "John ", i.e. with a space at the end. Or look for "* " using > "full cell" (not sure about the option in English since I use > the French version of Excel). > Thanks Denis, Lesley and Steve, Denis identified the problem exactly. Data was imported from 3 different sources 1. Created in Excel and had no space at the end of the names. 2. Screen captured and imported with a single space at the end of the names. 3. Screen captured and heavily edited resulting, in two spaces at end of names. So hence the sorting result was not as expected. Now the trick will be to remove all the unwanted spaces from 3000 cells, the lazy way. Any suggestions ? Using Google there is mention of the TRIM command but not how a novice can apply it within a spreadsheet. Keith