Re: [libreoffice-users] CALC convert text to numbers

On Sun, 17 Nov 2013 19:57:10 -0700
Oogie McGuire <oogiem@desertweyr.com> wrote:

> On Nov 17, 2013, at 4:41 PM, Paul <paulsteyn1@afrihost.co.za> wrote:
>
> > > Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE
> > > THAT IN LO)
> > How would you see it in Excel? I recall the display in Excel being
> > the same as in LO. The only difference between digits that are text
> > and digits that are numbers is in the justification.
> In Excel you can look at the formatting and if the formatting is as
> text you can change it and the numbers are now considered numbers not
> text. You do have to look at the cell format but once you do it's
> obvious as either text or numbers. 
Well in LO you can't change the data type just by changing the
formatting, but you should be able to see it! If you go to the format
of a cell, and the format is text, then the values in the cell are
text, but if the format is number, then the values are either numbers,
or text, in which case they will be preceeded by an apostrophe in the
input line. Although you say this isn't the case for you, this is how
it *should* work.

> > > All the discussions about the leading apostrophe are great but I
> > > still can't see it in LO nor can I remove it.
> > Cell by cell you can remove it, which is just the same as I remember
> > Excel working. You can see it, and remove it, in the input line per
> > cell. You can't see it in the actual cells, but you can't in Excel
> > either, as I recall.
> Actually you can't see it in LO at all, in the input line or
> anywhere. At least not on my machine, a mac. I've just re-verified
> this for the umptenth time. I'm attaching a bit of a screen grab that
> shows the input line in LO of a file where the cell I am looking at
> is actually text. No apostrophe showing at all, no obvious way to
> tell the thing is actually text and no way to change it easily.
As far as I can see from your screengrab, the number is left-aligned,
meaning it is text. In that case it won't show an apostrophe, because
the value is text in a cell formatted as text. It only shows an
apostrophe if the value is text in a cell formatted as a number,
because there is no need to mark text as text if it is in a cell
formatted as text. This is (as I recall from some years ago) the same
as Excel, I think.

> I wasn't able to get the text to columns to work on my spreadsheets
> so gave up on it. I haven't tried it again to see if it works. I'm
> also trying to get the extension mentioned a while back installed and
> running too. 
I haven't used that extension personaly, so I can't talk about that, but
the "Text to Columns" really should work. For me, I just select some
cells that have values that are text, but I want as numbers, then I
select "Text to Columns" from the "Data" menu, and click "Ok" in the
dialog that pops up. No need to do anything more.

Note that the selected cells have to be in a continuous range in a
single column. Also, for reference, my dialog settings are as follows:

Separator options:
  Separated by:
    Tab
    Comma
    Text delimiter: "

Nothing else selected.

I really hope this works for you. If not, let us know what is going
wrong, and we'll see if we can't get it working.

> Perhaps seeing the screen grab will explain, there is no feedback to
> the user at all on the data type in LO.
You mean apart from the justification?
Are you sure the apostrophe is not showing when you have a text data
value in a cell formatted as a number?
And apart from these two indicators, what other indicators does Excel
have (other than the fact that if you examine the format (in your
version) it tells you what the data type is)?

> The source for most of my files that I have to add is from an SQLite
> Database. The fields that are in the table are defined to be of type
> either real or integer (depends on the characteristic I am looking
> at) in SQLite. Once I get the proper query done in the database I
> save the results as a .CSV file, move the file from the machine that
> is doing the field sheep evaluations to my main desktop mac where I
> have my main spreadsheep. I bring up the .CSV file and the main sheep
> spreadsheet file up at the same time and cut and paste as required. I
> can't replace whole columns, my subset from the database often does
> not include data for all sheep but only for those individuals who got
> evaluated that particular day. My spreadsheet file has one row per
> sheep and then hundreds of data points for that specific animal in
> the columns. Since often groups of sheep get the same thing done on
> the same date I may have many individuals with data related to a
> specific item like a vaccine or a wormer. 
If you can do "Text to Columns" on the .csv file before copying the
data over, it would save you a lot of trouble, I think. We really need
to get that working for you.


On Mon, 18 Nov 2013 03:19:17 +0000
Brian Barker <b.m.barker@btinternet.com> wrote:

> In any case, just go to View | Value 
> Highlighting (or Ctrl+F8).  Text values will show in black and 
> numbers (and related quantities, e.g. currency and dates) in 
> blue.  These colours show despite existing text colour choices or 
> formatting choices such as negative currency values.  You can use 
> this temporarily whilst you are massaging your values and return to 
> normal view afterwards.
Thanks Brian! I haven't seen this before, and it's really useful.

-- 
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