Menu
What's New
By:
Filters

OpenOffice - Like I'm 5 please

WhiningSkeptic

WhiningSkeptic

Elite
Joined
Sep 28, 2014
Posts
3,902
Society
Apocalyptic
Avatar Name
Whining Skeptic Aboard
Good day,

I am trying to figure out what hides in my storage. In order to make life easier, I copied the "My items" list to an calc sheet in Open Office. That's all good and the list is displayed properly with each value in the right column.

However, when I try to sort it by PED value, it sorts it alphabetically. So instead of:

200
199
99

It goes:

199
200
99

Or something along those paths. The problem seems to be that my column is in text-format, however, I try and try and try but can't turn it into number format.

I was adviced by various posts in forums online to use the VALUE function to create a new column, but I only manage one row at a time.

Any guidance appreciated.
 
Assume you paste from My Items into columns A1 to D999.

Column D will hold the text value of PED.

Put this in E1 and copy down to E999: =VALUE(LEFT(D1;LEN(D1)-4))

Use column E to sort.

(ie take off the last 4 characters off the text string and turn the rest into a numeric value)
 
I wonder if it works similar to Excel.

I define the formula in the first row of the column and then drag the right corner of the cell down the column. That copies the formula to each row in the column.
 
KijkkiJikki said:
I wonder if it works similar to Excel.

I define the formula in the first row of the column and then drag the right corner of the cell down the column. That copies the formula to each row in the column.
Click to expand...

Yes, that works in OpenOffice too.
 
Serica said:
Assume you paste from My Items into columns A1 to D999.

Column D will hold the text value of PED.

Put this in E1 and copy down to E999: =VALUE(LEFT(D1;LEN(D1)-4))

Use column E to sort.

(ie take off the last 4 characters off the text string and turn the rest into a numeric value)
Click to expand...

Trying this at the moment and...

...all that happens is that the program crashes every time I try to paste =VALUE(LEFT(D1;LEN(D1)-4)) into the whole column... :banghead:

Going to try a few hundred at a time instead.

Edit: Just noticed some functions are in my local language, could that be an issue?
 
WhiningSkeptic said:
Trying this at the moment and...

...all that happens is that the program crashes every time I try to paste =VALUE(LEFT(D1;LEN(D1)-4)) into the whole column... :banghead:

Going to try a few hundred at a time instead.
Click to expand...

Don't paste in multiple cells, instead paste in one (correct D1 to your cell) and then drag it down, that way you will change D1 automagically to needed one.
 
Teiwaz Storm said:
Don't paste in multiple cells, instead paste in one (correct D1 to your cell) and then drag it down, that way you will change D1 automagically to needed one.
Click to expand...

Drag....? I've been pulling, stretching and hammering the column, but can't figure out how to drag something down. I think I might need Office 101.... :ahh:

Right now I don't even manage to get it working in one row at a time. Instead of the proper value returned in the cell, I get what I would translate to #NAME?.

Tried setting all settings to English but that doesn't help either. :scratch2:
 
as far as I understand you have the cells set to text. I use Excel usually and all that you do in Excel is to right click on the cell/s (drag to select multiple cells) select "format cells" the select "number" from the menu.

In order to imput a formula you need to put = in front of the formula or you'll get an error

Hold left mouse button down to drag and select
 
Often, when this happens, it's because the numbers weren't trimmed.

Instead of 34.19
You have 34.19(space)

select the number column, find/replace spaces into nothing and then changing the value types to numeral should work.

Oh btw, switch to Libre Offfice. Open Office isn't maintained as well anymore and the original OO staffs left to make LO.
 
WhiningSkeptic said:
Drag....? I've been pulling, stretching and hammering the column, but can't figure out how to drag something down. I think I might need Office 101.... :ahh:

Right now I don't even manage to get it working in one row at a time. Instead of the proper value returned in the cell, I get what I would translate to #NAME?.

Tried setting all settings to English but that doesn't help either. :scratch2:
Click to expand...

Don't know OpenOffice since I use Excel and GoogleDocs, so maybe something is different there...

Usually you select cell and then drag bottom right corner of it and drag on desired cells.

[]Here is example[/URL] for you with Serica's code.
Note that #VALUE! is because there is no string to convert to number in expected cells.
 
Last edited:
Teiwaz Storm said:
Don't know OpenOffice since I use Excel and GoogleDocs, so maybe something is different there...

Usually you select cell and then drag bottom right corner of it and drag on desired cells.

[]Here is example[/URL] for you with Serica's code.
Note that #VALUE! is because there is no string to convert to number in expected cells.
Click to expand...

I got the dragging working now, but still get the error message.

Managed to translate =VALUE(LEFT(D1;LEN(D1)-4)) to swedish properly, but that doesn't help. So not a language issue, but I'm doing something wrong.

Edit:
If anyone swedish reading this they could confirm, this is what I got with the help of the program:

=värde(VÄNSTER(D1;LÄNGD(D1)-4))
 
WhiningSkeptic said:
I got the dragging working now, but still get the error message.

Managed to translate =VALUE(LEFT(D1;LEN(D1)-4)) to swedish properly, but that doesn't help. So not a language issue, but I'm doing something wrong.
Click to expand...

Honestly I don't think you should translate formula, as it is an expression syntax, always in english.
 
Teiwaz Storm said:
Honestly I don't think you should translate formula, as it is an expression syntax, always in english.
Click to expand...

For some reason both OpenOffice and LibreOffice, which I downloaded now, has the functions translated. If I open up the tab with functions, select one, it will insert it into the cell.

For instance:

SUM(G1;G2) doesn't work while SUMMA(G1;G2) does. :scratch2:
 
WhiningSkeptic said:
For some reason both OpenOffice and LibreOffice, which I downloaded now, has the functions translated. If I open up the tab with functions, select one, it will insert it into the cell.

For instance:

SUM(G1;G2) doesn't work while SUMMA(G1;G2) does. :scratch2:
Click to expand...

Ok then, I'm a noob ;)
 
Not certain if it's the same in Open Office as it is in MS Office, but when I copy my item list into an excel spreadsheet, one thing I do is select the column that lists the PED amounts. Then I replace all "PED" with a blank space. After this, I am able to sort on values, instead of alphabetically. (might have to ensure your document is reading the values as numerical now, instead of alpha-numeric).

Otherwise, I got nothing. :silly2:
 
You know what, screw this.

I'm off to buy some paper, I'm doing this by hand....

Edit:

How can it not work!? Teiwaz PMd me a google document where it was working, I COPIED, and it stopped working.

Edit2:

Perhaps because I am an idiot and forgot to replace the As with appropriate columns....

Edi3:

Despite my stroke of genious ------------->>> :banghead::banghead::banghead:

Thanks for your patience.
 
Last edited:
WhiningSkeptic said:
How can it not work!? Teiwaz PMd me a google document where it was working, I COPIED, and it stopped working.

Edit2:

Perhaps because I am an idiot and forgot to replace the As with appropriate columns....
Click to expand...


First, make sure that if you copy formula, there is no second "=" added in front of it, sometimes happens.

Also, edit onlky top most formula to change A1 to proper celll, then drag formula like I explained in some post above.
 
Okay, so I am trying it step by step now.

Started with =LEFT(D1)

Which returned the value 9 which is correct, since that was the leftmost character in D1. Then I tried:

=LEFT(D1;3)

Which returned 9.9 which alos was correct, since that was the three leftmost characters in D1.

However, when I entered;

=LEFT(D1;(D1-4))

I get the error. Tried a few variations but did not get it to work. Still trying.

Edit:

I get LEN(D1) to return the proper length of D1 when I try in a separate cell, don't get it to work properly in the function though.
 
Last edited:
WhiningSkeptic said:
Good day,

I am trying to figure out what hides in my storage. In order to make life easier, I copied the "My items" list to an calc sheet in Open Office. That's all good and the list is displayed properly with each value in the right column.

However, when I try to sort it by PED value, it sorts it alphabetically. So instead of:

200
199
99

It goes:

199
200
99

Or something along those paths. The problem seems to be that my column is in text-format, however, I try and try and try but can't turn it into number format.

I was adviced by various posts in forums online to use the VALUE function to create a new column, but I only manage one row at a time.

Any guidance appreciated.
Click to expand...

Select the column and format cells as numbers.
 
Come on guys, you're doing it standing in a hammock. :laugh:

Select the column (or whole sheet), Ctrl+F, find: " PED", replace with: leave empty, Replace All - and voila.

likeyoure5.jpg


Disclosure: I do this in OpenOffice about two or three times a week.
 
Last edited:
Think I solved it!

I simply put the Length into column G, and inserted the value from G into the LEFT function, and subtracted 4 from that. Lol, yes, but it works. :yay:
 
Haruto Rat said:
Come on guys, you're doing it standing in a hammock. :laugh:

Select the column (or whole sheet), Ctrl+F, find: " PED", replace with: leave empty, Replace All - and voila.

likeyoure5.jpg


Disclosure: I do this in OpenOffice about two or three times a week.
Click to expand...

Sure, this worked too, but my workaround is much cooler. Better have that quad-core working. ;)
 
Last edited:
Haruto Rat said:
Come on guys, you're doing it standing in a hammock. :laugh:

Select the column (or whole sheet), Ctrl+F, find: " PED", replace with: leave empty, Replace All - and voila.

Disclosure: I do this in OpenOffice about two or three times a week.
Click to expand...

This will work but value will still be a string, so additional step to convert strings to numbers would be required in order to use values in calculations.


WhiningSkeptic said:
Think I solved it!

I simply put the Length into column G, and inserted the value from G into the LEFT function, and subtracted 4 from that. Lol, yes, but it works. :yay:
Click to expand...

Very strange, maybe you were missing one of parentheses before?
 
Teiwaz Storm said:
This will work but value will still be a string, so additional step to convert strings to numbers would be required in order to use values in calculations.




Very strange, maybe you were missing one of parentheses before?
Click to expand...

Strange indeed.

I'll try to get it work properly though, workarounds is not the proper way to learn things.

I got what I needed for the moment though. :)

Thanks to everyone who helped me out.
 
Teiwaz Storm said:
This will work but value will still be a string, so additional step to convert strings to numbers would be required in order to use values in calculations.
Click to expand...

Doesn't happen for me. Maybe if you have set up the column as 'Text' when pasting, but there's no reason to do that anyway.
 
I'm not sure why the Swedish version wouldn't allow nesting of the LEN() function. :scratch2:
But yes, putting that part of the formula in a separate column would work fine.

(For possibility of an extra space at the end of the text mentioned above ... in my own spreadsheet, I have an extra column that checks for the possibility of an extra space, and if there, removes it :) I didn't want to confuse WS though .. 5yo's get overwhelmed when shown too much information :D)
 
Serica said:
I'm not sure why the Swedish version wouldn't allow nesting of the LEN() function. :scratch2:
But yes, putting that part of the formula in a separate column would work fine.

(For possibility of an extra space at the end of the text mentioned above ... in my own spreadsheet, I have an extra column that checks for the possibility of an extra space, and if there, removes it :) I didn't want to confuse WS though .. 5yo's get overwhelmed when shown too much information :D)
Click to expand...

:D

It seems like all the functions are translated into the local language, like in the example for SUM() where it refused accept the english version. The function LEN had a swedish counterpart, so did VALUE. I find it strange, I'd expect it to use the english versions but apparently not, which is a huge downside, as most info online is in english.
 
When you downloaded off the OpenOffice site, did you ask for the English version or Swedish?
 
Serica said:
When you downloaded off the OpenOffice site, did you ask for the English version or Swedish?
Click to expand...

I can't recall, it was a long time ago.

If I know myself right, it should have been the english version.

I'll see if I can find some language setting somewhere and see what happens to the functions.

Edit: Not possible to set English in the UI, so must have downloaded a swedish version.
 
You must log in or register to reply here.
Back
Top