[Solved] Currency value taken as text

Discuss the spreadsheet application
Post Reply
xgenhanx
Posts: 6
Joined: Sat Dec 17, 2011 3:04 pm

[Solved] Currency value taken as text

Post by xgenhanx »

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.
Last edited by Hagar Delest on Wed Jan 04, 2012 9:44 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.3 on Windows 7
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Currency value taken as text

Post by 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.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Currency value taken as text

Post by acknak »

xgenhanx wrote: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.
AOO4/LO5 • Linux • Fedora 23
JJJoseph
Posts: 173
Joined: Thu Dec 13, 2007 2:55 am
Location: Vancouver, Canada

Re: Currency value taken as text

Post by 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.
OOo 3.4.X on MS Windows 7 + Blackberry 10
techpo
Posts: 5
Joined: Wed Jan 04, 2012 3:57 pm

Re: Currency value taken as text

Post by 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?
OpenOffice 2.4 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Currency value taken as text

Post by 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.
AOO4/LO5 • Linux • Fedora 23
JJJoseph
Posts: 173
Joined: Thu Dec 13, 2007 2:55 am
Location: Vancouver, Canada

Re: Currency value taken as text

Post by JJJoseph »

acknak wrote: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 :-)
OOo 3.4.X on MS Windows 7 + Blackberry 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Currency value taken as text

Post by RoryOF »

JJJoseph wrote: 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Currency value taken as text

Post by Villeroy »

JJJoseph wrote: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.
JJJoseph wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JJJoseph
Posts: 173
Joined: Thu Dec 13, 2007 2:55 am
Location: Vancouver, Canada

Re: [Solved] Currency value taken as text

Post by 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.
OOo 3.4.X on MS Windows 7 + Blackberry 10
Curious Wayne
Posts: 2
Joined: Sat Aug 18, 2012 4:31 pm

Currency value taken as text Redux

Post by 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” ;)
You can always tell a Texan... but ya can't tell 'em MUCH!
quitego
Posts: 1
Joined: Tue Sep 11, 2012 11:23 pm

Re: [Solved] Currency value taken as text

Post by 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
OpenOffice 3.3.0 on Windows 7
Curious Wayne
Posts: 2
Joined: Sat Aug 18, 2012 4:31 pm

Re: [Solved] Currency value taken as text

Post by 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 /nformatted /detect special [N]umbers

:bravo:
You can always tell a Texan... but ya can't tell 'em MUCH!
Post Reply