[Solved] Sorting data numerically

Discuss the spreadsheet application
Post Reply
fst
Volunteer
Posts: 152
Joined: Wed Nov 28, 2007 2:31 pm

Re: Sorting data numerically

Post by fst »

Hi,

what's about using the Search & Replace facilities of Calc ?

Select the column you want to change from Text to numbers
Format-Cells-Numbers-General
Ok Button
CTRL+F
Search for '.*' without the quotes
Replace with &
Under more tag selection only and regular expressions
Click on Ok and the numbers should be numbers now.

Frank
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Sorting data numerically

Post by huw »

badtoad1963 wrote:That trick seems to work. But what a pain in the posterior.
Presumably, you used the trick in Excel?
Edit: Answerd by PM
badtoad1963 wrote:Creating a temporary new column and setting it =VALUE the column I wanted to sort by worked in OOo. I don't even have Excel installed anymore.
which was TerryE's helper column suggestion, directly above badtoad1963's post - I just wasn't reading the thread thoroughly.
Hagar de l'Est wrote:Note that I also tried to Find and Replace the apostrophe (that tags the content as text) but it doesn't work :evil:
You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.
Last edited by huw on Thu Nov 29, 2007 11:00 am, edited 1 time in total.
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK
Contact:

Re: Sorting data numerically

Post by TerryE »

This is the most elegant method so far, but it still highlights Calcs bizarre behaviour as to when it coerces to numeric.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
Hagar Delest
Moderator
Posts: 30690
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sorting data numerically

Post by Hagar Delest »

huw wrote:You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.
Thanks huw and Frank !
I tag the thread as solved. badtoad1963 post again if you have any issue.
LibreOffice 7.2.6 on Xubuntu 22.04 and 7.2.6 portable on Windows 10
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

I'm not sure but I think I'm sorry I asked :roll: :)
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

I guess this is a painfully technical discussion for a simple question.

I know we've had this discussion time and again, but this is yet another strike against the way that Calc handles this (a numeric value stored as a string). It is simply wrong for badtoad to have to worry about Calc's inner object model just for doing a reasonable sort. If Calc is going to insist on not converting data behind the user's back (which is a worthy goal), then it should at least warn you when something "looks fishy" and give the user some idea what the issue is and how to fix it. I know I've seen Excel warn about it, but I don't even see anything under Tools > Detective for Calc.

I think the fact that, internally, Calc is inconsistent should be another clue that this is not the way to go. The search & replace is a handy trick, but it is surprising to me in the extreme. Is there any rationale for why a search & replace on a text value should cause it to change from a string to a number?

I'm used to Perl, which will coerce data in both directions naturally, along with providing standard idioms for guaranteeing string or numeric evaluation. It works very very well and I wish Calc would fix this huge problem.

There are going to be major changes in Calc's formula handling for OOo 3.0 (next fall) to comply with ODFF (Calc/ODFF Implementation [OOo wiki]). Unfortunately (last I checked) ODFF had punted on this issue and did not specify any 'correct' behavior. If this is ever going to be changed, however, I think this would be the time to do it.
AOO4/LO5 • Linux • Fedora 23
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Sorting data numerically

Post by huw »

Hagar de l'Est wrote:Note that in the file here, I can't manage to make any calculation at all. I just have the formulas (and they're not to be displayed in the Tools>Options configuration.
That's because the file was imported into Excel as forced text (the problem didn't emerge until the file was opened in OpenOffice due to the two programs' different handling of textual numbers):
badtoad1963 wrote:I originally created the file in Excel by importing a slightly modified assembly language file and kept everything formatted as text, as I wasn't planning on manipulating the data.
A formula entered into a cell formatted as text will always display as just a formula, not its result.

NB. See jrkrideau's PDF of spreadsheet problems for a brief summary of how badly both Excel & OpenOffice treat textual numbers when referred to in a formula. Issue 5658 & issue 58903.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

Interesting links huw, thanks.

View > Value Highlighting is the option I was thinking of, although it still isn't ideal.
AOO4/LO5 • Linux • Fedora 23
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

I'm sorry to butt in, but I can't get the workaround to work. I do a search for .* and get nothing.

Are we talking about when we ctrl+a the spreadsheet>Data>Sort>columnx, then the sort shows the numbers are not arranged in 1,2,3,200 rather 1,2,200,3?

I'm really stuck here. Help?
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

The solution that worked for me is to create a temporary column and "copy" the data from the column whose data you want to sort by. Do this by entering "=VALUE(original data cell)" into the top cell of the new column and dragging it to the last cell in the column. If this doesn't make sense I will try to explain better.
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

I'll give it a shot. Thanks!
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

Ok, I'm almost there, except I have 50,000+ rows. Dragging, will take some time.

However I'm not clear on what I should do after I copy the data from the original column. Do I paste it into the temp column with "=VALUE(original data cell)"?
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

Say you want to sort your data according to Column A. Create a temporary Column B. Enter into cell B1 "=VALUE(A1)". This sets the contents of cell B1 equal to the value of cell A1. No copying or pasting required. Now drag this by the corner and copy it to all the cells below and it should auto increment the contents and effectively "copy" column A to column B. As long as Column B is not formatted as text you should have it. Now replace the original Column A with the new Column B and sort!
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

I do a search for .* and get nothing.
Edit > Find & Replace
Search for: .*
Replace with: &
Options: Regular expressions = YES

I suspect you forgot to select that option.
AOO4/LO5 • Linux • Fedora 23
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

@acknak: That is true, I forgot that part. Still this did not make it possible to sort numerically.

@badtoad1963: I get "ERR:502" on every line I drag.

I forgot to mention that the column contains text as well, like this: 54 KB. And each line contains different numbers, but same text. Maybe I'll Experiment with the format cells user-defined.

Thanks the both of you. I'm sure I'll figure this out eventually.

*Decided to post the problematic column.
Attachments
CALC.SORT.NUM.ods
(57.61 KiB) Downloaded 369 times
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

I found a workaround the text that is in the columns containing numbers. I deleted "KB" from the column. Then I sorted it. Then I added the KB back into the column using the double quotations in the format cells properties.

Thanks all for the help.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

Good work! That is in fact the best solution, if you really need the "KB" displayed there. As the unit text is now part of the cell format and not the cell data, the numeric sorting will work without special steps from here on.
AOO4/LO5 • Linux • Fedora 23
User avatar
Hagar Delest
Moderator
Posts: 30690
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] Sorting data numerically

Post by Hagar Delest »

abelmiah wrote:Are we talking about when we ctrl+a the spreadsheet>Data>Sort>columnx, then the sort shows the numbers are not arranged in 1,2,3,200 rather 1,2,200,3?
That's because the content is considered as text. Select the cells, and in the Format menu, apply the Number format.
LibreOffice 7.2.6 on Xubuntu 22.04 and 7.2.6 portable on Windows 10
Post Reply