![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
thanks!
I just tried this extension. It's easy to use and seems to work very well.
Is there a way to move its icon to a different place on the tool bar?
Thanks,
-Bill
I ended up using this option for this time, create a column using the VALUE function, then copy and paste special numbers only.
For future I will look at other options. This is something that will be done a lot for the next year or so.
I am in the process of converting my sheep records from a LO Spreadsheet system into an SQLite database along with writing my own flock management program, LambTracker. During the writing/conversion I am maintaining both systems because I have a requirement to keep records for the federal government and I can't risk a bug in my SW screwing up the database or making my records/reports inaccurate. So while I am still in the writing/debugging (estimated to be the next year for initial work, then another year to fine tune) I need to maintain both systems.
I will look at other options that will automate the process for the future but I needed to get something done quickly as I had to report the results this week.
I have a spreadsheet that is the output of a SQLite Database on Android. It contains numbers that have been formatted as text. When I copy the columns into my LibreOffice Spreadsheet I want to make sure that they are interpreted as numbers. However they come in as text and the manuals and help at Libre Office are less than useful.
I found reference to a value function, but no information on how to apply it to this data. I tried just changing the format of the cells to be number but that didn't do anything at all. I've also attempted various styles of paste special but still it doesn't work. There has got to be a way to quickly say this text is all really numbers and get it working!
There are various ways to do this, and you may want to experiment in order to discover what suits your work flow.
The VALUE() function? Yes, you can use that:
o In a spare column, row, or range (as appropriate), or even on another sheet, enter =VALUE(Xn) - where Xn is the start of the range.
o Fill the formula down the column, along the row, or through the range. You now have a copy of your data - but as numbers, not text.
o If desired, copy the numeric values and paste them back over the originals, but using Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V) instead of ordinary Paste.
o In the Paste Special dialogue, remove the tick from "Paste all" if necessary and ensure that Numbers and Formats are ticked but Formulas is not ticked.
o You can now delete the temporary values or delete their rows or columns if you wish.No, changing the format of the cells will not help: a cell's format affects the way its value is displayed and the way inserted values are handled, but not the actual contents of the cell.
Another easy way is via a comma-separated-value file. Save the sheet with your rogue text values using File | Save As... and selecting "Text CSV (.csv)" for "Save as type:". Now open the resulting (temporary, scratch) CSV file in LibreOffice and you will have numeric values that you can copy where you need them. Yes - as has already been suggested - you could alternatively import the CSV file as an additional sheet in your existing spreadsheet document.
I trust this helps.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA
What I would consider a bug is that changing the format of a cell doesn't change the data.
If I set a cell to be number then even if the text was defined as a text field using the ' sign I expect that to be stripped out and the field displayed and function properly as a number.
Just like i consider it a bug that if I select all cells in a spreadsheet and change the font or the font size I expect the contents of all cells to be modified to the new settings and it doesn't happen that way.
The format of the cell
doesn't change this behavior, it only changes the *display* of the
contents, not the interpretation of the contents. At least, as I
understand it.
Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA
For me dealing with an extension, installing it, making sure it doesn't conflict with something else was more effort than creating a column, using Value() and then pasting special.
What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, if you change the format of a cell the conversions happen without any problems. I want that same behavior in Calc because to me it makes sense that the cell format should be the controlling factor for what type of data is in a given cell.
Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui
Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA
Paul,
The problem is with the symbol ' It can't be searched and replaced. That's why Ady consider it a bug.
The only solution, which I used yesterday, is multiply by one.
More than a bug, we must consider it an incomplete implementation of the meaning of ' to accept figures as text. The operator should be reachable from Calc interface and not hidden.
Hi,
What's a problem is that in Excel even though it also uses the
leading ' to format text as numbers, if you change the format of
a cell the conversions happen without any problems.
That´s not true. I just tried in Excel. The leading ' stays, no
matter what format you would apply.
to me it makes sense that the cell format should be the
controlling factor for what type of data is in a given cell.
Absolutely no! Format must never have an impact on values or data
types. That´s a matter of logics.
Cheers,
Stefan
Could this possibly be a 'feature' associated with a certain version of office\excel?
Steve Gruspier
Electrical Engineering
As already said, this is all expected and happens in Excel also. There is some long technical reason why, I'm sure someone has written about it online if you google a bit 
Best,
Joel
The problem is with the symbol ' It can't be searched and replaced.
That's why Ady consider it a bug.
That's because it isn't actually there. It's just an indicator of the
fact that the number is not a number, but a text string. You can't take
it out to make the text string a number. You need to replace the
text string with an actual number (that may *look* the same, but
isn't).
The only solution, which I used yesterday, is multiply by one.
I still don't understand what that is supposed to do. It sounds like a
kludge to me. The text string is a text string, I'm not sure why
anybody would think multiplying a text string by 1 would give you a
number.
More than a bug, we must consider it an incomplete implementation of
the meaning of ' to accept figures as text. The operator should be
reachable from Calc interface and not hidden.
Again, as explained before by myself and Brian, I think, it's *not*
hidden, it just isn't part the the cell value. It is purely an
indicator of what the cell value is, either text or numeric. It *is*
reachable from the interface, in-as-much-as you can edit the cell
contents and either put in a string marker, or remove a string marker.
This is not a bug or incomplete feature. This is expected behaviour and
works like other spreadsheet programs.
Paul
Not in my version I just verified it. Had to go install Excel again to see but it works as I expect
Macintosh Snow Leopard running MS Excel X for mac.
That´s not true. I just tried in Excel. The leading ' stays, no
matter what format you would apply.
Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA
What I would consider a bug is that changing the format of a cell
doesn't change the data.
And it shouldn't, it changes only the *format* of the data, not the
data itself.
I often get confused by this, expecting that when I change the format,
my data has changed, but format is just about how the data is
displayed, not about what the data is. Mostly this is absolutely
correct, and format should *absolutely not* change the data. Most of
the time when I have been expecting format to change the data is when I
have values that are text instead of numbers, and I format as numbers
and then expect to have numbers in the cell. There should be an easy
way to change from numbers to text and back (and the same for dates and
any other data types), but format isn't the way.
Just like i consider it a bug that if I select all cells in a
spreadsheet and change the font or the font size I expect the
contents of all cells to be modified to the new settings and it
doesn't happen that way.
This doesn't work for you? I'm surprised. I haven't checked, but I'm
fairly sure it works as expected for me.
Paul
Try it, or I can send you a spreadsheet that fails to change the fonts and you can sew what I mean.
This doesn't work for you? I'm surprised. I haven't checked, but I'm
fairly sure it works as expected for me.
Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA
1)
When data is in the spreadsheet, is simple to do it in one column.
Select the cell(s) to convert from text to number in one column.
Menu/Data/Text to columns - Separating by (nothing selected)
2)
With Search & Replace
Search for: .*
Replace with: &
With regular expression selected in Other options.
Miguel Ángel.
Paul,
Make this test.
1. Open a new worksheet. Format some cells, say from A5:A10 as text. Write some numbers on those cells.
2. Copy those cells to other column, say to C5:C10 and format as number (format @). You will see that the cells now shows an ' before the numbers.
3. Write a formula in other cell, multiplying with 1, for instance, E5=C5*1
Now check the cell and you will discover that you have a number.
Therefore, those numbers with ', that in reallity are text, can be multiplied by 1, to transform it to a number.
As a side note, I was not able to use the method of copy -- paste multiplying by one.
Spreadsheets use the MVC (model view controller) paradigm. That means
that the model (how the data are actually stored) and how you view
the data are separated. You can take a number like 40000.5 and view it
as a date, a date and time, a real number, etc. You can easily compare
dates because they are stored as numbers, not character strings like
"Monday, Nov. 11." Further, you can easily send your spreadsheet to
someone who only knows some language you have never heard of and s/he
can open it and display and compare the dates in whatever language
s/he has set.
The best way to see if a cell contains a number, text, or a formula is
to use View -> Value Highlighting (F8). (Does Excel even have this
feature? If so, it must hidden in the ribbon somewhere.) A zero as
text has the ASCII value 48; as a number, the value is 0, so text and
numbers are not equal. OpenOffice used to generate errors if one
improperly tried to add text and a number, for example. Along the
way, that behavior was modified to emulate Excel. (I prefered the
old way along with the fact that either OOo or gnumeric or both used to
evaluate -1^2 correctly--the mathematical answer is -1, not 1.)
I just checked using Excel 2010, if you change the format (the view) of the
cell, the underlying representation (the model) does not change.
1. Type '123 in a cell, say A1
2. Right click and choose Format Cells, then Format as a number.
(That is, change General to Number.)
The entry is still text. You can confirm because =sum(A1) yields 0.
Note: =A1+0 yields 123. (Also the text is still left justified.)
That is, there is no conversion.
Best regards,
David Gast
With Excel 2010 I made the following test, that contradicts your assertion.
A) Write just: 123 --> That's a number (just to compare).
B) Write : '123 --> That's text. Sum(cell) is equal to cero.
C) Multiply: In another cell write a formula that
references '123 address and multiply by 1.
You get a number!
Check it.
Yes.
o Click the down-arrow at the right end of the relevant toolbar and select Customize Toolbar... .
o Under Toolbar Content, scroll down and select the relevant icon.
o Use the up and down arrows at the right of the Commands panel to move the icon to where you prefer it.
o OK.
I trust this helps.
Brian Barker
Brian
If you are using a Mac 10.9 then you can move a Tool Bar icon simply by
clicking on the gap to the right of the icon.
When the "object" frame appears, drag the fame to where you want the icon,
let go and the icon will move.
I use CT2N when I copy and paste from a web site and it works, with one
exception.
If the number is prefixed by a currency sign, CT2N does not work.
Cor very kindly gave me a solution.
In your Macros you will find CT2N
In the module Main Code, find function Check for Text in String.
There you can find the line, 'new in 1.2.0: allow for negative numbers:
-=45, (=40,)=41
Elseif j=45 AND i=1 Then'
You can then insert before, or immediately after that line, 'Elseif j = 163
AND i = 1 Then ' allow for £'
(Ignore the ' at the beginning and the end of the insert.)
This also works, Many thanks, Cor.
Tink.
It does not contradict my assertion. I did not discuss multiplication.
The answer is that it depends on how you multiply. You did not
state how you multiplied but I am sure you used the * operator.
Using the * operator, you will get 123. If you use product(a1),
where a1 contains '123, you will get 0.
Operators and functions do not work the same.