[users] Re: How to sort mixed alpha & numbers?
On 09/15/2009 07:34 PM, jomali wrote:
> Put the formula
> =MID(A1;FIND(" ";A1);LEN(A1))+0
> in Cell B1.
> Extend down as far as your data in Column A goes.
> Sort on column B
> Voila!
>
> John
Thank you, thank you, thank you! :-)
-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Re: How to sort mixed alpha & numbers?
Hi Gary,
NoOp wrote (17-9-2009 2:10)
On 09/15/2009 07:34 PM, jomali wrote:
Put the formula
=MID(A1;FIND(" ";A1);LEN(A1))+0
in Cell B1.
Extend down as far as your data in Column A goes.
Sort on column B
Voila!
I'm having trouble figuring that out... wouldn't be the first time :-)
Would it be OK if I emailed you the spreadsheet so that you can try with
the original?
I tried and it does work perfect. (Better at RC ?? ;-) )
Alternative:
=VALUE(MID(A1;FIND(" ";A1);LEN(A1)))
Ciao - Cor
--
Cor Nouws
- nl.OpenOffice.org marketing contact
- Community Contributor Representative in the Community Council
Gevoel niet vrij te zijn? Zie www.nieuwsteversie.nl
-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
[users] Re: How to sort mixed alpha & numbers?
On 09/16/2009 05:31 PM, jomali wrote: > Sure, go ahead. I'd be glad to look at it. > > John Thanks. It's on it's way (only 22.1KB) - email subject is "Re: How to sort mixed alpha & numbers?" Gary - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Re: How to sort mixed alpha & numbers?
Sure, go ahead. I'd be glad to look at it.
John
On Wed, Sep 16, 2009 at 8:10 PM, NoOp wrote:
> On 09/15/2009 07:34 PM, jomali wrote:
> > Put the formula
> > =MID(A1;FIND(" ";A1);LEN(A1))+0
> > in Cell B1.
> > Extend down as far as your data in Column A goes.
> > Sort on column B
> > Voila!
> >
> > John
>
> I'm having trouble figuring that out... wouldn't be the first time :-)
> Would it be OK if I emailed you the spreadsheet so that you can try with
> the original?
>
> Gary
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.org
> For additional commands, e-mail: users-h...@openoffice.org
>
>
[users] Re: How to sort mixed alpha & numbers?
On 09/15/2009 07:34 PM, jomali wrote:
> Put the formula
> =MID(A1;FIND(" ";A1);LEN(A1))+0
> in Cell B1.
> Extend down as far as your data in Column A goes.
> Sort on column B
> Voila!
>
> John
I'm having trouble figuring that out... wouldn't be the first time :-)
Would it be OK if I emailed you the spreadsheet so that you can try with
the original?
Gary
-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
[users] Re: How to sort mixed alpha & numbers?
NoOp wrote: On 09/12/2009 01:51 AM, Bob Estes wrote: NoOp wrote: On 09/09/2009 08:35 PM, NoOp wrote: I've tried every combination that I can think of (even formating cells to text) and still cannot figure out how to sort via a column with data like this: Sail Number USA 84 USA 604 USA 275 USA 35024 USA 103 USA 116 USA 38747 USA 72 USA 40 USA 87549 USA 41001 USA 52 USA 28719 Any suggestions? Thanks to all that offered suggestions... I'll have to give them a try when I'm back from sailing on Monday. Gary (NoOp) I found a way that works reasonably well. First, I copied the list into column B of a Calc spreadsheet with each entry on its own line. I then deleted the USA from each row and reinserted it in column A. As part of the test, I added a couple of entries each for UK and AUS numbers. I then selected the data to be sorted. I then selected "DATA/SORT, Sort by column A then by column B". On the options tab, I then selected "Enable natural sort". Clicking on OK sorted the data first by country and then by number. This was an alphabetical sort instead of an ASCII sort which is what I think you wanted. This sounds like a long procedure, but it took me longer to write up the explanation than it did to do the sort. I hope this helps. Bob Thanks all. The regatta is over so now I'll have time to experiment. What I did (the night before the regatta) was copy the data from a web page (http://www.big-boat-series.com/Event/CurrentEntries.aspx) and pasted into Calc. I had hoped to sort by sail numbers so that I'd have an easy reference list as the boats come around my marks (I work on-water Race Committee). Turns out I survived without it, but I still want to figure out the sort puzzle for future situations. A 96 boat list isn't that hard to manipulate, but anything larger might require a macro if I can't figure out a simple way to do it with the sort feature. If all the sail numbers are from the same country (USA) it's very simple. Copy the sail numbers into column two with each sail number in its own row. Using the find/replace/all find "USA" and replace it with nothing. This will delete all instances of "USA". Next, insert "USA" in column one next to the first number. Select all of the cells in column one from the first number to the last number including both the first and the last cell. Select Fill/Down and "USA" will be inserted into all of the cells. It gets a little more complicated if you have entries from more than one country. You'll have to repeat the procedure for each country code. Now you can do the sort. The most time consuming part of this procedure is copying each sail number into its own row. Maybe someone else has a fast way of doing that. I hope this helps. Bob - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Re: How to sort mixed alpha & numbers?
Put the formula
=MID(A1;FIND(" ";A1);LEN(A1))+0
in Cell B1.
Extend down as far as your data in Column A goes.
Sort on column B
Voila!
John
On Tue, Sep 15, 2009 at 6:30 PM, NoOp wrote:
> On 09/12/2009 01:51 AM, Bob Estes wrote:
> > NoOp wrote:
> >> On 09/09/2009 08:35 PM, NoOp wrote:
> >>> I've tried every combination that I can think of (even formating cells
> >>> to text) and still cannot figure out how to sort via a column with data
> >>> like this:
> >>>
> >>> Sail Number
> >>> USA 84
> >>> USA 604
> >>> USA 275
> >>> USA 35024
> >>> USA 103
> >>> USA 116
> >>> USA 38747
> >>> USA 72
> >>> USA 40
> >>> USA 87549
> >>> USA 41001
> >>> USA 52
> >>> USA 28719
> >>>
> >>> Any suggestions?
> >>
> >> Thanks to all that offered suggestions... I'll have to give them a try
> >> when I'm back from sailing on Monday.
> >>
> >> Gary (NoOp)
> >
> > I found a way that works reasonably well. First, I copied the list into
> > column B of a Calc spreadsheet with each entry on its own line. I then
> > deleted the USA from each row and reinserted it in column A. As part of
> > the test, I added a couple of entries each for UK and AUS numbers. I
> > then selected the data to be sorted. I then selected "DATA/SORT, Sort
> > by column A then by column B". On the options tab, I then selected
> > "Enable natural sort". Clicking on OK sorted the data first by country
> > and then by number. This was an alphabetical sort instead of an ASCII
> > sort which is what I think you wanted. This sounds like a long
> > procedure, but it took me longer to write up the explanation than it did
> > to do the sort.
> >
> > I hope this helps.
> >
> > Bob
>
> Thanks all. The regatta is over so now I'll have time to experiment.
> What I did (the night before the regatta) was copy the data from a web
> page (http://www.big-boat-series.com/Event/CurrentEntries.aspx) and
> pasted into Calc. I had hoped to sort by sail numbers so that I'd have
> an easy reference list as the boats come around my marks (I work
> on-water Race Committee).
> Turns out I survived without it, but I still want to figure out the sort
> puzzle for future situations. A 96 boat list isn't that hard to
> manipulate, but anything larger might require a macro if I can't figure
> out a simple way to do it with the sort feature.
>
>
>
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.org
> For additional commands, e-mail: users-h...@openoffice.org
>
>
[users] Re: How to sort mixed alpha & numbers?
On 09/12/2009 01:51 AM, Bob Estes wrote: > NoOp wrote: >> On 09/09/2009 08:35 PM, NoOp wrote: >>> I've tried every combination that I can think of (even formating cells >>> to text) and still cannot figure out how to sort via a column with data >>> like this: >>> >>> Sail Number >>> USA 84 >>> USA 604 >>> USA 275 >>> USA 35024 >>> USA 103 >>> USA 116 >>> USA 38747 >>> USA 72 >>> USA 40 >>> USA 87549 >>> USA 41001 >>> USA 52 >>> USA 28719 >>> >>> Any suggestions? >> >> Thanks to all that offered suggestions... I'll have to give them a try >> when I'm back from sailing on Monday. >> >> Gary (NoOp) > > I found a way that works reasonably well. First, I copied the list into > column B of a Calc spreadsheet with each entry on its own line. I then > deleted the USA from each row and reinserted it in column A. As part of > the test, I added a couple of entries each for UK and AUS numbers. I > then selected the data to be sorted. I then selected "DATA/SORT, Sort > by column A then by column B". On the options tab, I then selected > "Enable natural sort". Clicking on OK sorted the data first by country > and then by number. This was an alphabetical sort instead of an ASCII > sort which is what I think you wanted. This sounds like a long > procedure, but it took me longer to write up the explanation than it did > to do the sort. > > I hope this helps. > > Bob Thanks all. The regatta is over so now I'll have time to experiment. What I did (the night before the regatta) was copy the data from a web page (http://www.big-boat-series.com/Event/CurrentEntries.aspx) and pasted into Calc. I had hoped to sort by sail numbers so that I'd have an easy reference list as the boats come around my marks (I work on-water Race Committee). Turns out I survived without it, but I still want to figure out the sort puzzle for future situations. A 96 boat list isn't that hard to manipulate, but anything larger might require a macro if I can't figure out a simple way to do it with the sort feature. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: How to sort mixed alpha & numbers?
NoOp wrote: On 09/09/2009 08:35 PM, NoOp wrote: I've tried every combination that I can think of (even formating cells to text) and still cannot figure out how to sort via a column with data like this: Sail Number USA 84 USA 604 USA 275 USA 35024 USA 103 USA 116 USA 38747 USA 72 USA 40 USA 87549 USA 41001 USA 52 USA 28719 Any suggestions? Thanks to all that offered suggestions... I'll have to give them a try when I'm back from sailing on Monday. Gary (NoOp) I found a way that works reasonably well. First, I copied the list into column B of a Calc spreadsheet with each entry on its own line. I then deleted the USA from each row and reinserted it in column A. As part of the test, I added a couple of entries each for UK and AUS numbers. I then selected the data to be sorted. I then selected "DATA/SORT, Sort by column A then by column B". On the options tab, I then selected "Enable natural sort". Clicking on OK sorted the data first by country and then by number. This was an alphabetical sort instead of an ASCII sort which is what I think you wanted. This sounds like a long procedure, but it took me longer to write up the explanation than it did to do the sort. I hope this helps. Bob - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: How to sort mixed alpha & numbers?
On 09/09/2009 08:35 PM, NoOp wrote: > I've tried every combination that I can think of (even formating cells > to text) and still cannot figure out how to sort via a column with data > like this: > > Sail Number > USA 84 > USA 604 > USA 275 > USA 35024 > USA 103 > USA 116 > USA 38747 > USA 72 > USA 40 > USA 87549 > USA 41001 > USA 52 > USA 28719 > > Any suggestions? Thanks to all that offered suggestions... I'll have to give them a try when I'm back from sailing on Monday. Gary (NoOp) - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: How to sort mixed alpha & numbers?
"NoOp" wrote in message news:h89s6m$2t...@ger.gmane.org > I've tried every combination that I can think of (even formating cells > to text) and still cannot figure out how to sort via a column with > data like this: > > Sail Number > USA 84 > USA 604 > USA 275 > USA 35024 > USA 103 > USA 116 > USA 38747 > USA 72 > USA 40 > USA 87549 > USA 41001 > USA 52 > USA 28719 > > Any suggestions? Hmm, I was sure I'd done that before, but ... seems to not work for me; so much for my gray matter! If only machines would do what we MEANT to do, not what we TOLD them to do!! lol Maybe just some thinking out loud will help you think of something: Right now it's doing a "machine sort", sorting on the first number and ignoring the length of the whole number. So if all the sail numbers were the same length, it would do what you need. I thought there would be a way to force the display of every number to be the same length using leading zeroes but I can't find a way to do that, even using currency or Custom. A reasonably simple macro could be created though to make every number the same length as long as the total length were known to be 5 (or whatever). Or enter them all originally as 5 digits, using leading zeroes. 00084, 00604, etc.. Better, a formula could be used to pad them out to 5 digits. It would be a long IF statement, one section per length that could exist, but that would have the advantage of maybe creating the leading zero list and formulae in hidden columns for the final product. pseudo-code: (If length=2, 000##, else nothing; (if length=3,00###, else nothing, etc.. (...(...(...) Now sort, then remove leading zeroes, display list. Or, redo the list by length. Have a column of 2 digits, another 3 digits, another for 4, 5, up to whatever te max is. The re-assemble after sorting into one list. More complex formulae though and a pain to keep track of. I have a feeling though I'm simply missing the right Calc setting to make it sort properly. There about has to be one. Guess I'll have to sit back and watch :-( Twayne - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
