Page 1 of 1

Text to numbers in CSV file, forumla popup

PostPosted: Tue May 10, 2016 7:05 am
by Crash-14
Two points here, one is not a bug-
First, I've noticed when converting report numbers (all numeric) to a standard format using CONCATENATE, example: cell A2 is 12345 and cell B1 is 2015, I combine them as 201512345 I end up with a text output. Here's my process:
After updating the column, I then copy the formula results and paste them (w/o formula update) over the original numbers so I can delete the formula column.
The results are always text. This happens when I check the convert text to numbers box when pasting.

The only option I have to convert them to numbers is to save and close then reopen the file, where the convert text to numbers does actually work.

Using a GSC format does the same thing, except there is no text to number option upon reopening. I'd love to be able to work in these files as a GSC and then save the final product as a CSV.

Am I missing something or is this how it is supposed to work? I know in Libre Calc there is an apostrophe before the number to flag text, and the apostrophe can be removed via find & replace.

Next, the v.16 formula popup is irritating. It often blocks the cells I need to see in order to enter data for the formula. Is there a way to avoid this as was the case in v.15?

Thank you.

Re: Text to numbers in CSV file, forumla popup

PostPosted: Thu May 12, 2016 8:12 am
by citadel5
Ad. 1) This is correct. The retuned value of such a formula is a text string/label so any further actions have to treat it initially as text.
Why don't you use the "Edit > Convert Cells > Formula To Values" command for a given range of such formulas?
Then you can use the "Edit > Convert Cells > Labels To Numbers" commands.

Ad. 2) Yes, you can hide it. Click the cell indicator button above any edited cell and on the displayed menu uncheck the current formula list display option. If all of them are unchecked, no list will be displayed when editing cells.

Re: Text to numbers in CSV file, forumla popup

PostPosted: Sat May 28, 2016 9:00 pm
by Crash-14
Ad. 1) This is correct. The returned value of such a formula is a text string/label so any further actions have to treat it initially as text.
Why don't you use the "Edit > Convert Cells > Formula To Values" command for a given range of such formulas?
Then you can use the "Edit > Convert Cells > Labels To Numbers" commands.

Thanks for the info, that solved the main problem I was having.

Ad. 2) Yes, you can hide it. Click the cell indicator button above any edited cell and on the displayed menu uncheck the current formula list display option. If all of them are unchecked, no list will be displayed when editing cells.

I'm not sure what the cell indicator button is? I clicked on the button next to the drop-down list for cell types and did not see that option.

Reading the help file, I can double-click on a formula cell and I get a small button in the upper left with the cell number. It gives three options:
Show list of functions- Always
Show list of functions- After entering '='
Show list of functions- When entering '=' And for empty cells

This does not affect the drop-down formula box that appears when I go to edit a formula. I may have misstated what is happening. When I go to add a formula in a cell, clicking in the formula box in the toolbar causes the list of formulas to drop down, not pop up as I had written. If the cell is directly under the drop down, I cannot see the cells under it. In v.15, the formula functions did not drop down like this.

I don;t want the drop down function.

Re: Text to numbers in CSV file, forumla popup

PostPosted: Sun May 29, 2016 6:58 pm
by citadel5
As a matter of fact, these settings concern editing cells within the table. If you're using the "Formula" edit field (on the toolbar) to edit and enter formulas then there is no option to turn off the formula list. I'll place this (adding some setting for that edit field) on the to-do list.
Note that you can also resize this list/dialog box (independently for table cells and that edit field) and "shrink" it considerably.