[users] Re: Mass edit in calc field - help!
Brewster Gillett wrote, [..] I'm attaching a stripped-down 200 rows or so of it as a sample. 200 rows gives a fair range of the dates, and of course displays the evidence that the column is *not* being sorted by date, but as text numerical data :-) It's only 22K, so shouldn't have any difficulty getting through... ...thanks for your efforts. Some, but not all of the dates have leading apostrophes. I did this: Open the .ods file in OOo Calc. File|Save as... and pick .csv format. Close Calc. When I used a plain text editor, there were no leading apostrophes in any of the dates. Find the .csv file you saved. Right-click|Open in OOo Calc. You should get presented with an input filter dialog. Click the column heading for the date column (it will initially say Standard), then just above that is a drop down for Column type. Pick Date (MDY) (I presume that's how you dates are). Then the column should import as proper dates and you can format appropriately. [OOo 3.2.1, Windows] -- Bob Long - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
Adam Tauno Williams wrote: =DATEVALUE($CELL) will convert a text representation of a date to a date value. No, it won't unless you set the application locale in the options to something that matches the assumed date format in the text values. Usually people import from csv using the wrong locale and get a wild mixture of text and wrong dates with switched month and day. 1/2/99 = 1st of February vs. Jan, 1st 1/31/99 is not a valid date unless you interprete it in US context. This is hard to fix. First apply the right locale in the language options. Then switch back day and month for all numbers and convert text: =IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1) ; VALUE(A1)) - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
Brewster Gillett wrote: bg: Doesn't work. Whether I enter it as =DATEVALUE($CELL) or as =DATEVALUE($THEDATECELL) it still echoes #NAME? all the way through to the final paste. What am I doing wrong? Brewster Gene Young wrote: Try substituting the actual cell reference in place of THEDATECELL eg; =DATEVALUE($E5) where cell E5 is the cell that contains the date. Gene bg: Silly me. Shows how seldom I've ever had to delve this far into Calc, doesn't it? I didn't get that Adam was expressing a reference. I just so seldom need to dick around with any sort of formulas in cells. So I tried it as you describe. Got a 522 error msg. (iteration, it says - and even after reading the description I am not certain how it applies to this case.) I sent a sample of the file at Richard Detwiler's request. Maybe it will reveal its secrets :-) Thanks, Brewster Spreadsheets are very complex and verstile tools. Somewhat like a simplified programming language. It is extremely simple to get something in and out of a sheet. It can be extremely hard to get the right stuff in and out. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
Brewster Gillett wrote: It's only 22K, so shouldn't have any difficulty getting through... thanks for your efforts. Brewster The values have been imported/pasted/entered as text (sequence of digits with slashes). Dates you can calculate with have to be numbers. Your locale settings assume D/M/Y dates, so 1/30/99 is not a numeric expression (month=30). 1/2/99 is a numeric expression (1st of Feb), but obviously it is meant to be Jan, 2nd. Since you imported all the values as text, you see the leading apostroph which (luckily) prevents the wrong evaluation. ToolsOptions...LanguageSettingsLanguages: Locale = English(USA) Now all the text values get the leading apostrophe *in order to* prevent data corruption (keep the user's text values! do not auto-convert user data!). When you re-enter some of the values manually, you'll see that you get numeric values in any format you can freely choose from the number formatter. The number formatter is pointless with text values. This is how to re-enter all the dates in one go: Select them. EditFind...[Ctrl+F] [More Options] [X] Current Selection [X] Regular Expressions Search: .+ [yes, a dot and a plus] Replace: [Replace All] I'm sure this helps. Andreas - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
I forgot: After you got the correct numeric values, you can switch back to your normal locale setting. As already outlined, this has no effect on existing data. Instead of the global application locale you may also set the cells' number format locale before you start the searchreplace operation. FormatCells...tab:Numbers, right side number format locale Choose number format @ (Text) when you need to convert numbers to text. This special number format inhibits all evaluation even without the leading apostrophe. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Multipart/Related Content-type
On 22 September 2010 17:39, Julien CHEVALIER julien.cheval...@l-b-c.frwrote: Dear List, I used to open a spreadsheet having the Multipart/Related Content-type and an .xls file extension. I'm not shure how it was generated, but probably something like Cognos with MS Excel 2002 export option. It's broken since I upgraded to OO 3.2 Can anyone help ? Regards -- Julien Chevalier If you want OpenOffice.org to save a spreadsheet in Excel format you need to ask it specially; it doesn't do that by default. You need to use FileSave As, then select the appropriate MS format (Excel 97, Excel 95 or Excel 5), then ensure that the little Automatic filename extension box is ticked (checked) and then enter the name you want to give the spreadsheet without any .xls extension. OpenOffice.org uses ODF format (Internationally accepted ISO standard) unless told otherwise. If you want to change the default i.e. to have OpenOffice.org *always* save in Microsoft format (unless told otherwise for a particular case), go to ToolsOptionsLoad/SaveGeneral and choose the relevant format for each type of dcument - two drop down lists (Document type and Always save as) near the bottom of the pane. -- Harold Fuchs London, England Please reply *only* to users@openoffice.org
Re: [users] Mass edit in calc field - help!
Brewster Gillett wrote: Adam Tauno Williams wrote: Insert a column enter =DATEVALUE($THEDATECELL) copy-and-paste that one cell to the rest of the column copy the date value column paste as values over the old column delete the column you created Richard Detwiler wrote: Is there any way you can send a small portion of the file with any confidential information removed or altered? (or post it on a web site) I'm not able to recreate the problem you're having, which I suspect is because I don't have the same type of CSV file that the data originated from. bg: I guess that's possible, although I am working with it saved as an Open Office .ods file, not in the originally received .csv version. All I ever do with the .csv versions is strip off the unneeded columns to make them conform to my template - all further work on them is then done in the saved-as-.ods version. I'm attaching a stripped-down 200 rows or so of it as a sample. 200 rows gives a fair range of the dates, and of course displays the evidence that the column is *not* being sorted by date, but as text numerical data :-) It's only 22K, so shouldn't have any difficulty getting through... ...thanks for your efforts. Brewster Brewster: I opened your file, went to cell D2, and entered the formula =DATEVALUE(C2). It gave me an integer number, 40198, which I knew was encouraging. Then when I formatted the cell as a date (choosing the 12/31/99 option), it gave me the date 01/20/99, which is what it should be. Then it's a simple matter of copying cell D2 down through as many rows as you need.
[users] How to search a table and return a list of values?
Is there some magical combination of functions that allows one to search a table of values and return a list of values? My table has dates across the top row, and names down the side. Within the table are Y, N, or blank. I want to get the list of names based on the Y's in a particular column (which corresponds to a date). Oct 4Oct 6Oct 8 Oct 9 Mark YY SamY Y Frank Y NY Henry Y YYY Searching for Oct 4 returns the string Mark, Frank, Henry. Searching on Oct 6 return the string Sam, Henry. The list of names can not be placed in alphabetical order. I could probably use a macro, if I knew how to use macros;-) I would rather just have a formula, but perhaps that is not possible? Thanks, Mark
[users] Huge Excel Spreadsheets Crash OO.o-3.1
I'm trying to convert client spreadsheets from .xlsx to .ods in OO.o-3.1 on my Slackware-12.2 system. The application crashes when I try to save-as or, sometimes, when I move to a different sheet in the workbook. The .xlsx file size is about 7.5M and I have 2G RAM and 2G swap on this machine. Any ideas on how to convert these huge documents will be appreciated. Thanks, Rich - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Huge Excel Spreadsheets Crash OO.o-3.1
Rich Shepard ha scritto: I'm trying to convert client spreadsheets from .xlsx to .ods in OO.o-3.1 on my Slackware-12.2 system. The application crashes when I try to save-as or, sometimes, when I move to a different sheet in the workbook. The .xlsx file size is about 7.5M and I have 2G RAM and 2G swap on this machine. Any ideas on how to convert these huge documents will be appreciated. Thanks, Rich - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org The first thing that comes to mind is try the latest version of OpenOffice. IME compatibility with MS Office file formats gets better with every new version. HTH Marcello - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Huge Excel Spreadsheets Crash OO.o-3.1
On Thu, 23 Sep 2010, Marcello Romani wrote: The first thing that comes to mind is try the latest version of OpenOffice. IME compatibility with MS Office file formats gets better with every new version. Marcello, I wondered about this. I'll do this. Thanks, Rich - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Comparing two strings - resort to compare using bash - OOcalc has no substring functions
I have columns that contain addresses. Column A is number and street as in '1234 N Main' Column B is the building and unit number as in 'F456' Column C is the address as it would be placed on an envelope: '1234 N Main F456' I need to indicate in another column if the combination of column A concatenated to column B is exactly the same as column C. In OO calc, When concatenated, it becomes '1234 N MainF456' (trailing spaces are deleted by OOcalc) so they never can be equal. So, I'm reverting to using the .csv of the spreadsheet, and then using bash (in Linux) to do the compare where I can extract specific characters, then rebuilding the .csv using bash. CLI always comes through when GUI fails! By the way, the newly re-written help functions on the Internet for calc still tell you to use ';' when only '.' is valid. John - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
Richard Detwiler wrote: Brewster: I opened your file, went to cell D2, and entered the formula =DATEVALUE(C2). It gave me an integer number, 40198, which I knew was encouraging. Then when I formatted the cell as a date (choosing the 12/31/99 option), it gave me the date 01/20/99, which is what it should be. Then it's a simple matter of copying cell D2 down through as many rows as you need. Richard, Your application locale is English(US). Brewster has some other locale setting. This is why the conversion must fail. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Mass edit in calc field - help!
I'm attaching a stripped-down 200 rows or so of it as a sample. 200 rows gives a fair range of the dates, and of course displays the evidence that the column is *not* being sorted by date, but as text numerical data :-) It's only 22K, so shouldn't have any difficulty getting through... ...thanks for your efforts. Brewster On Thu, 2010-09-23 at 13:16 -0400, Richard Detwiler wrote: Brewster: I opened your file, went to cell D2, and entered the formula =DATEVALUE(C2). It gave me an integer number, 40198, which I knew was encouraging. Then when I formatted the cell as a date (choosing the 12/31/99 option), it gave me the date 01/20/99, which is what it should be. Then it's a simple matter of copying cell D2 down through as many rows as you need. bg: It all works just as you describe, but with a terminal flaw; once having done all that, and admiring the shiny new date-formatted column, absent the apostrophes, I essay to perform an ascending-order sort on it. Alas, Evo's sort is apparently *not* reading it as a real live date, because instead of parsing it as YY (or - I tried it both ways)then MM then DD, the sort function is reading the two leading digits, and ignoring the rest. This of course is exactly what the sort function was doing when the column still was in text rather than date format :-( It would appear, maybe, that the sort function is lacking an appreciation of the distinction between a straight text number and a date. Either that, or the FORMAT,CELL process produces something that has the appearance of a date, but not the reality ?? Appreciate all the help - I am running out of ideas. I will go try some of the other combinations that have been suggested. Thanks, Brewster -- *** Embrace a sharing community of sustainable justice low-carbon diversity *** W. Brewster Gillett b...@fdi.usPortland, OR USA *** Simply because you don't like to hear it, that doesn't make it untrue. *** - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Re: Mass edit in calc field - help!
On Thu, 2010-09-23 at 21:26 +0200, Andreas Säger wrote: Richard, Your application locale is English(US). Brewster has some other locale setting. This is why the conversion must fail. But, Andreas, my Evo was *not* set to any locale other than the default for my version; English (USA). where did you get the idea that it was set otherwise? I've checked it two or three times. Am I missing something in your recommendation? Are you maybe saying, in some of your other messages, that I need to temporarily change the locale setting to something *other* than English (USA) in order to perform the format change, then change it back to English (USA)?? Or is there another locale setting named English(US), distinct from the default one of English (USA)? Thanks, Brewster -- *** Embrace a sharing community of sustainable justice low-carbon diversity *** W. Brewster Gillett b...@fdi.usPortland, OR USA *** Simply because you don't like to hear it, that doesn't make it untrue. *** - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] table - dismantling
In .odt documents, I often wish to take text which has been enclosed in a table and remove the table - while keeping the text. Obviously I can copy the text to the document just outside the table, then delete the table-and-contents. Is there another way? Tony, www.godornot.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] table - dismantling
On 9/23/2010 4:04 PM, Tony Carr wrote: In .odt documents, I often wish to take text which has been enclosed in a table and remove the table - while keeping the text. Obviously I can copy the text to the document just outside the table, then delete the table-and-contents. Is there another way? Tony, www.godornot.org Yes, there sure is. Just click in the table and Table Select, then Table Convert Table to Text. Select how you want the resulting text to be represented (for instance, you can use the default selection to keep the basic structure with tabs between the contents of the cells from a given row, and a new line for each row) and click the OK button, and you should have it. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] Re: Mass edit in calc field - help!
Brewster Gillett wrote: But, Andreas, my Evo was *not* set to any locale other than the default for my version; English (USA). where did you get the idea that it was set otherwise? I've checked it two or three times. Because you had trouble with Adam's DATEVALUE conversion. Now I see that your problem was not related to the locale. Locale settings are the main source of trouble, though. Nevertheless, no formatting will do the the conversion from text to number. It has to be done with a formula in another column or in place using the findreplace. It may be important to know that none of the methods work with your text-dates and a non-US setup. I've got to activate the US locale before I can convert the data in your file. With a non-US locale DATEVALUE returns Err:502 for the values with months12 (1/31/99). I get wrong dates with switched month and days for those values with a valid month number in the middle (1/2/99). - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Comparing two strings - resort to compare using bash - OOcalc has no substring functions
On Friday 24 Sep 2010 00:28:46 John Bowling wrote: I have columns that contain addresses. Column A is number and street as in '1234 N Main' Column B is the building and unit number as in 'F456' Column C is the address as it would be placed on an envelope: '1234 N Main F456' I need to indicate in another column if the combination of column A concatenated to column B is exactly the same as column C. In OO calc, When concatenated, it becomes '1234 N MainF456' (trailing spaces are deleted by OOcalc) so they never can be equal. So, I'm reverting to using the .csv of the spreadsheet, and then using bash (in Linux) to do the compare where I can extract specific characters, then rebuilding the .csv using bash. CLI always comes through when GUI fails! By the way, the newly re-written help functions on the Internet for calc still tell you to use ';' when only '.' is valid. John snipped unsubscribe info John, I tried it out - on Linux, running OOo310m19(build 9420) [3.1.1-19-13.fc11]. I have cell A1 with 1234 N Main [space after the Main] B1 with F456 [no spaces before or after the value] C1 has the formula =concatenate(A1;B1), and results in 1234 N Main F456 [which is what I expected - the space after 'Main' is preserved] Note that I have enclosed the values/formula/results with double quotes in this email for clarity : they are not there in the Spreadsheet. I am afraid I have no clue to the problem, unless I can see the file in question. Should you so desire, and if you have no sensitive info, or can sanitize the info, can you send me the file, off the list, so that I can look at it ? Pradeep Sinivas on 24 Sep 2010, Friday, IST (which is GMT +05:30) -- --- Goofy Definitions! Jury: Twelve people who determine which client has the better lawyer. --- Signature block is /auto/ /generated/ from the Unix utility *fortune* fortune run at 06:10 IST on 24 Sep 2010, Friday - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Comparing two strings - resort to compare using bash - OOcalc has no substring functions
On 2010-09-23 14:58, John Bowling wrote: I have columns that contain addresses. Column A is number and street as in '1234 N Main' Column B is the building and unit number as in 'F456' Column C is the address as it would be placed on an envelope: '1234 N Main F456' I need to indicate in another column if the combination of column A concatenated to column B is exactly the same as column C. In OO calc, When concatenated, it becomes '1234 N MainF456' (trailing spaces are deleted by OOcalc) so they never can be equal. So, I'm reverting to using the .csv of the spreadsheet, and then using bash (in Linux) to do the compare where I can extract specific characters, then rebuilding the .csv using bash. CLI always comes through when GUI fails! By the way, the newly re-written help functions on the Internet for calc still tell you to use ';' when only '.' is valid. John John: Try this function: =Exact(A2 B2;C2) 'Exact' determines if two strings are identical. I padded a space between the two cell values to get the correct format. TomW - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Mass edit in calc field - help!
Brewster Gillett wrote: I'm attaching a stripped-down 200 rows or so of it as a sample. 200 rows gives a fair range of the dates, and of course displays the evidence that the column is *not* being sorted by date, but as text numerical data :-) It's only 22K, so shouldn't have any difficulty getting through... ...thanks for your efforts. Brewster On Thu, 2010-09-23 at 13:16 -0400, Richard Detwiler wrote: Brewster: I opened your file, went to cell D2, and entered the formula =DATEVALUE(C2). It gave me an integer number, 40198, which I knew was encouraging. Then when I formatted the cell as a date (choosing the 12/31/99 option), it gave me the date 01/20/99, which is what it should be. Then it's a simple matter of copying cell D2 down through as many rows as you need. bg: It all works just as you describe, but with a terminal flaw; once having done all that, and admiring the shiny new date-formatted column, absent the apostrophes, I essay to perform an ascending-order sort on it. Alas, Evo's sort is apparently *not* reading it as a real live date, because instead of parsing it as YY (or - I tried it both ways)then MM then DD, the sort function is reading the two leading digits, and ignoring the rest. This of course is exactly what the sort function was doing when the column still was in text rather than date format :-( It would appear, maybe, that the sort function is lacking an appreciation of the distinction between a straight text number and a date. Either that, or the FORMAT,CELL process produces something that has the appearance of a date, but not the reality ?? Appreciate all the help - I am running out of ideas. I will go try some of the other combinations that have been suggested. I'm not sure what you mean by Evo's sort. When I do this, everything sorts fine. See attached file. To explain something about dates, which might help grasping the situation, dates are stored as numbers, with 12/31/1899 being 1, 1/1/1900 being 2, 1/1/2000 being 36526, etc. All the formating does is take those numbers and put them into a date that humans can recognize as a date. You can choose multiple ways of displaying the date, but it doesn't change the underlying number. It is the underlying number that is used in sorting. So I'm not sure why, when you convert the text dates into real dates that Calc can use, things don't sort. Maybe you should send your file again, this time after you've converted the text dates to real dates. sampleCalcOOusersRD.ods Description: application/vnd.oasis.opendocument.spreadsheet - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
