Hello,
I am trying to download an excel spread sheet from ebay. Every time I do, the column with prices has an apostrophe before the number so I cannot add up the columns. My older version didn't do this. Please help! Thank you, Carol LTS Tools, Inc 40 Conway St South Deerfield, Ma 01373 413-775-3026 phone |
On 03/04/18 12:04 AM, LTS Tools, Inc wrote: > Hello, > > I am trying to download an excel spread sheet from ebay. Every time I do, the column with prices has an apostrophe before the number so I cannot add up the columns. My older version didn't do this. Please help! If you download a Excel file that the problem is with the Excel file. If you copy data into into an OpenOffice file or open a csv file into OpenOffice tick the 'Detect special number" box in the Import window. > > Thank you, > Carol > LTS Tools, Inc > 40 Conway St > South Deerfield, Ma 01373 > 413-775-3026 phone --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
At 10:04 02/04/2018 -0400, Carol Noname wrote:
>adding up columns in excel If you are adding up columns in Microsoft Excel, you may need to look elsewhere for help. Perhaps you mean in an OpenOffice spreadsheet? >I am trying to download an excel spread sheet from ebay. Every time >I do, the column with prices has an apostrophe before the number so >I cannot add up the columns. If you mean that you can see the apostrophes in the cells themselves, that is very odd. But you probably mean that when a cell value is displayed in the Input Line it shows with a leading apostrophe: that is a standard part of spreadsheet behaviour. This indicates that what you have in a cell with a numeric format (including a currency one) is actually text - which may look like a number - and not a genuine number. This may be because the author has intentionally entered text instead of numbers, has entered currency amounts in what is not the standard currency for his or her locale, or because of the way in which you have incorporated the material into your spreadsheet document. If the distinction between number and text which looks like a number is not obvious, just consider your (US) zip code, which you quote as "01373". That is text. If it were a number, you could quote it instead as 1373 - but you cannot. If it were a number, you could read it as "one thousand, three hundred [and] seventy-three" - but you'd never do that. If it were a number, there would be some special significance in the postal location that happened to have exactly twice its value - 02746 - but there isn't. After all, it is called a "zip code", not a "zip number". So there *is* a use for text which happens to look like a number - not what you want here. >My older version didn't do this. I hope it did, as this is correct spreadsheet behaviour. This spreadsheet document may be different from previous ones, or you may have incorporated the material in a different way. >Please help! o If you are pasting material into a spreadsheet, you should see the Text Import window. Tick the "Detect special numbers" box and your text values will be converted to genuine numbers. (This works for currency values as well as for numbers.) But this won't help if you are actually downloading and opening a spreadsheet document, as you say. When you have text values in your spreadsheet, there are various ways to convert them to real numbers: o You can use the VALUE() function to derive the values you need into a separate column. If you wish, you can then paste them back over the originals, but using Edit | Paste Special (or Ctrl+Shift+V) instead of ordinary Paste, and ensuring the Formulae is *not* ticked in the Paste Spacial dialogue. You may need to format the cells as Currency in order to add or replace the currency symbol. o Select the material. Go to Data | Text to Columns... . (The "Detect special numbers" box should be ticked.) Your text values will be converted to numbers. But here is an interesting trick: you can add the text values without converting them to numbers. If you want to add up, say, the values in A1 to A99, you might use the formula =SUM(A1:A99) As you have found, if you use that with your text values masquerading as numbers, it will not work. How about incorporating the VALUE() function to convert the values on the fly? You might try =SUM(VALUE(A1:A99)) - but that doesn't work either, as the VALUE() function cannot take a range as its parameter. But what does work is this: enter =SUM(VALUE(A1:A99)) as the formula in the cell, but instead of pressing Enter or the (green tick mark) Accept button in the Input Line to complete the entry, press Ctrl+Shift+Enter. This creates an array formula, which does work. (You will need the result cell to be formatted as Currency for its value to behave correctly.) Note that when you have done this, your formula will appear in the Input Line surrounded by braces, i.e. as {=SUM(VALUE(A1:A99))} - but you *cannot* achieve the effect by entering these yourself: you must instead use Ctrl+Shift+Enter to complete the formula. I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
In reply to this post by Martin Groenescheij
Ticking the "Detect special number" box worked! Thank you!
Thank you, Carol LTS Tools, Inc 40 Conway St South Deerfield, Ma 01373 413-775-3026 phone In a message dated 4/3/2018 12:54:28 AM Eastern Standard Time, [hidden email] writes: On 03/04/18 12:04 AM, LTS Tools, Inc wrote: > Hello, > > I am trying to download an excel spread sheet from ebay. Every time I do, the column with prices has an apostrophe before the number so I cannot add up the columns. My older version didn't do this. Please help! If you download a Excel file that the problem is with the Excel file. If you copy data into into an OpenOffice file or open a csv file into OpenOffice tick the 'Detect special number" box in the Import window. > > Thank you, > Carol > LTS Tools, Inc > 40 Conway St > South Deerfield, Ma 01373 > 413-775-3026 phone |
Free forum by Nabble | Edit this page |