[Solved] Currency value taken as text

[Hagar Delest]

When I copy and paste dollar values into my spreadsheet, a leading apostrophe is attached to the dollar value. '$115.91
It doesn't appear in the cell, only in area that displays the cells that are to be summed.

[Hagar Delest]

Hi and welcome to the forum!

Please don't post in a topic that is not related to your problem, especially if it has been solved. I've split your post.

Don't type the dollar sign else it will be taken as text (hence the apostrophe). Use the currency formatting instead (the button in toolbar or Format>Cell then Number tab).

Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[acknak]

When I copy and paste dollar values into my spreadsheet, a leading apostrophe is attached to the dollar value. '$115.91

This means that Calc is importing the value as text, rather than as a numeric value.

My guess is that you're pasting text and getting the text import dialog window. If that is the case, then all you have to do is make sure that the "detect special numbers" option is checked, and Calc will import the currency items as numeric values and apply an appropriate cell format.

If that doesn't help, then you'll need to explain exactly what you're doing, step by step.

[JJJoseph]

I'm another user having the same problem importing from a CSV file. The leading apostrophe prevents using the number as a number. Selecting "Detect Special Numbers" has no effect. I'm able to complete this problem using Excel, but I'd like to learn how to do it within OpenOffice. Example: The following is how the CSV file appears in its native (text) form:

"Completed","USD","-52.42","0.00","-52.42"

However, when imported by Calc, the data appears as:

Completed |USD |'-52.42|'0.00|'-52.42 (The "|" symbol is used here to denote a Calc cell)

Note that Calc has prepended an apostrophe to each number, e.g. '-52.42, which ensures that -52.42 remains as a text item and is not imported to a number. Excel senses that it's a number and imports it as such.

I've been asking this question every year with no success. The reason that it comes up yearly is that PayPal and others export annual summaries in CSV format, but Calc can never figure out the data. Is there any other way to ensure that Calc imports a number as a real, functioning, number (like Excel is able to do), without blocking any subsequent manipulation? It's the bizarre prepended apostrophe that causes so much grief because it blocks changing the text value to a number value. None of this would be a problem if Calc didn't add that weird, unasked for, apostrophe.

[techpo]

This is a really weird problem. Well I know the problem when I download an excel sheet and calc does something different with it when I open it, but I never had apostrophes in front of the numbers (sometimes I add them manually, to avoid that the text or number sget changed because of a different command). I can feel lucky Maybe it's really a problem of the CSV format?

[acknak]

The problem is that Calc stupidly follows the rules, while Excel and everyone else does not. Technically, Calc's behavior is perfectly correct, but practically, it's not helpful.

Do you see the quotation marks around your numbers: "-52.42"? That means that the value should be interpreted as text, which is exactly what Calc is doing. The leading apostrophe is an indicator that Calc is forcing the entry to be taken as literal text and not a numeric value.

When you import the data, make sure the "Quoted fields as text" option is OFF (unchecked), and select comma as the field separator and " as the text delimiter. That works for me with your sample.

[JJJoseph]

When you import the data, make sure the "Quoted fields as text" option is OFF (unchecked), and select comma as the field separator and " as the text delimiter. That works for me with your sample.

Thank you, acknak, I finally figured that out. The trick is to remember it NEXT year when the next batch of MS standard CV files (from Google, Yahoo, PayPal, eBay, etc) rolls in. If OO doesn't make any progress with MS CSV handling, and keeps inserting that hated apostrophe where it's not wanted, it looks like I'll be back here again with the same question

[RoryOF]

If OO doesn't make any progress with MS CSV handling, and keeps inserting that hated apostrophe where it's not wanted, it looks like I'll be back here again with the same question

Surely not! You will, of course, have searched the Forum using the Forum search box on top right of screen and found the answer. It is always possible to find one's previous posts by using the "View your posts" link towards top left of screen.

[Villeroy]

If OO doesn't make any progress with MS CSV handling,

You are just ignorant when you do not see the massive improvements.
Older versions never put the apostrophe unless you explicitly marked a numeric column as text. If you change the format in order to convert wrongly imported text data to numbers you are even more ignorant.
In older versions all quoted numerals used to be numbers unless you explicitly marked them as text. Older versions had no locale option in the import dialog. In some cases you had to switch the locale before importing.
OOo 3.3 can import anything without assumptions nor automatism. It's all up to you and you have all the required options in one dialog. OOo3 remembers the file import options.

MS CSV handling

MS CSV handling requires MS software. MS Excel has a very bad reputation regarding import and export of text tables.

CSV is a database exchange format and I get good results with less effort when I import text data through the Base component into all kinds of spreadsheets and text documents. But that might be far over your head.

[JJJoseph]

Well, I'm suitable humbled by your blistering insults. I trust that you're feeling better now? Please, let me know when you've recovered and we can get back to the topic at hand.

[Curious Wayne]

I hate to rehash this but I can't seem to find an actual resolution anywhere. I regularly cut and paste blocks of dollar amounts (with leading $s) from my browser to calc 2.4 without any problem. When I upgraded to OOo 3.3, a leading apostrophe gets attached to the dollar value – I need the numeric value.

I am NOT getting or using an import dialog window or using a CSV file. I am only using paste or paste special. I have downgraded to 2.4 in order to get my work done.

Is there any solution allowing me to paste dollar amounts in calc 3.x (short of pasting to a text file, editing it, and repasting to calc)?

BTW, JJJoseph; you did not deserve the reprisal but remember... “never attribute to malice what can be adequately explained by ignorance”

[MrProgrammer]

If you haven't pasted yet, use Edit > Paste Special > Unformatted Text to get the import dialog.
If you've already pasted, convert text cells to numbers with Data > Text to Columns to get the import dialog.

Pasting from a browser is frequently a source of trouble because browser text (called HTML) contains many hidden attributes which tag the data in ways that you can't see. Pasting as unformatted text discards all of those attributes and retains only the data values. Because there are no attributes, Calc opens the text import dialog so you can supply attributes to the data and tag columns as numbers, text, MDY dates, DMY dates, YMD dates, etc.

[quitego]

I was having similar difficulty whenever I wanted to import payments from an internet website via copy and paste, the dollars cell data turned up as text. I scoured this forum site and found a few hints leading to this solution. This actually is a combination of 2 formulas.

1. to convert the imported dollar figure text into text still, this formula is

=RIGHT(F4;LEN(F4)-2)

where F4 is the cell to be converted. The -2 refers to the 2 digits '$ and the space' we want to rid of.

2. convert the above data into number using the value formula

=VALUE(G4)

where G4 is the calculated cell of formula 1 above. Now confirm conversion to numeric value by \View \Value Highlighting and there it is - blue.

3. A shortcut combination of the 2 above is

=VALUE(RIGHT(F4;LEN(F4)-2))

where F4 is the cell to be converted.

ps. Following success on this last formula in the cell e.g. I4 - the same formula can be used in the series (highlight I4 down I5:I10 for example) to apply for the rest of imported data using the \Edit \Fill \Down

Please give me feedback if this is repetition or already dealt with or if new and helpful to you. Thanks

[Curious Wayne]

My problem was solved with the “detect special numbers” option. I became confused because paste special, detect special seemed redundant. However selecting paste special, unformatted, and detect special numbers seemed a little cumbersome since I use this to paste from my browser about 50 times every day (and sometimes I forget to detect special).

Here's a hot tip for those who want to paste “special numbers” in a single command. The solution is a very basic autohotkey script. For those who don't know, Autohotkey is a free, simple Windows programming language designed to aid productivity by allowing you to (temporarily) redefine keys, etc. You can download it here: <http://www.autohotkey.com/>.

The script is:

^!v::
Send !es{enter}u{enter}!n{enter}
return

This defines Control+Alt+V (or any other key combo) to [E]dit /paste [S]pecial /[U]nformatted /detect special [N]umbers