[Solved] Apostrophe before value

Post by **Martin.Flynn** » Thu Apr 03, 2014 1:45 pm

I've had problems with this before but haven't posted.
My problem is that I occasionally get an apostrophe in front of a number value throughout a column (or row) and then can't sum these values until I remove the apostrophe's cell by cell. Is there a way to remove all the apostrophe's in one go? I've tried reformatting the column/row but that doesn't work.

Johnnydon's solution works.

Thanks to all.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 03, 2014 2:05 pm

Enter your numbers without leading apostrophe.

Post by **Martin.Flynn** » Thu Apr 03, 2014 2:37 pm

I haven't entered any apostrophes. They just seem to appear occasionally. Might be that the cells are formatted wrongly initially. I'd like to be able to remove them, like I said, all in one go. Is that possible?

edit this topic's initial post · Post by **Villeroy** » Thu Apr 03, 2014 2:39 pm

So how do you fill out that sheet?
Removing the quotes is one thing, getting correct values into that sheet is another.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 03, 2014 4:01 pm

Please post some example values having an apostrophe and some others having none.
Please post what you find under menu:Tools>OPtions>LanguageSettings>Languages>Locale (the 2nd option on that dialog).
Hit Ctrl+F8 (or View>Highlight Values) and tell us if some of the numbers appear in blue font.
It is possible to remove all apostrophes in one go leaving you with completely wrong data.

Post by **MTP** » Thu Apr 03, 2014 9:37 pm

The problem cells may be formatted as text. (The apostrophe is spreadsheet code for "what come after this is text and not a number".) To prevent future problems, highlight all the cells you'll be working with, right-click and choose "Format" and make sure the cells are of type "Number".

To fix the current apostrophe problem, I believe you can use find and replace (Edit -> Find & Replace). As long as there are no apostrophes you want, just find ' and replace with nothing for the whole sheet.

Edit: Well, I tried that and it didn't work. What does seem to work is highlighting the problem cells (it's OK if other cells are included, too, as long as it's OK for them to be numbers), go to Data -> Text to Columns, and hit 'OK'.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 03, 2014 10:18 pm

Cells that had been formatted as text before data entry contain text without showing a leading apostrophe. Any leading apostrophe would be the first character of the text.
"Normal" cells with any other number format can have text when you add the leading apostrophe. '=A1*A2 gives a text with = as first character. '00123 gives a text with 0 as first character. The apostrophe supresses all kind of evaluation (number, formula or auto-completion) just like the leading = triggers formula evaluation.
Cells that had been imported as text mark any numeric expressions as text so the numeral won't eveluate. There are at least 6 import/link methods from all types of sources from copy&paste up to database connections. There are some good reasons why one deliberately wants to import numerals as text.
The locale plays an important role. Importing or typing some USAmerican numerals into a spreadsheet that assumes British or "continental" input will interprete some numerals correctly, some wrongly and some others not at all. OpenOffice can handle all these ideosyncrasies very well but you may get an incredible mess of wrong data if you don't know how to import correct values. If Martin did not type the apostrophes, then he or someone else wrongly imported data into the spreadsheet. For instance, people tend to completely ignore important options of the text import dialog. Others paste from arbitrary HTML pages hoping that some aritificial intelligence turns culturally determined text into correct data.

JJJoseph · Post by **JJJoseph** » Mon Apr 07, 2014 7:43 am

Villeroy wrote:If Martin did not type the apostrophes, then he or someone else wrongly imported data into the spreadsheet.

It's not "someone else" that makes this mess, it OO Calc up to v3 that does this when opening .csv files. It's an incredibly retarded, undocumented Calc move that's very difficult to undo after importing .csv data. If it's "cultural" protection as Villeroy suggests, then it Calc should offer an easy-to-understand way to undo it. It requires a regular expression search operation:

Earlier, user Acnak suggested a regular expression search & replace:

Edit > Find & Replace
Search for: .* (period+apostrophe)
Replace with: & (ampersand)
Options/Regular expressions: ON (checked)
Click "Replace All"

God knows why, but this works to clear out the hidden apostrophes that are bunging up your worksheet. I've also noticed that this problem doesn't appear to happen in the recent v4, but I can't swear that other problems might have emerged

edit this topic's initial post · Post by **Villeroy** » Mon Apr 07, 2014 8:07 am

The problem is that people ignore import options all together and that they have zero spreadsheet knowlege (numbers vs. text). They do not even hit the help button on that dialog.
When the program interpretes numerals, some people complain because they want literal text (before version 3.3). When the program imports text, others complain about missing numbers (since version 3.3).
You are free to write a text import wizard for the total spreadsheet noobs. Be warned: people will complain because they do not understand why you ask them such silly questions instead of simply open that darn file. Oh, and even when the spreadsheet imports wanted numbers, people complain that the spreadsheet does not automatically apply the same number format (which is a completely crazy idea from a programmers point of view).
Version 4.1 will have the "Special Numbers" option checked by default. Lets see how people will compain about interpreted numerals that they don't want to be interpreted.

Edit > Find & Replace

As already mentioned, any such method may leave you with completely wrong data if you don't understand what you're doing. The question remains unanswered until the OP shows us some of the data in question.

JJJoseph · Post by **JJJoseph** » Mon Apr 07, 2014 7:36 pm

The correct solution is not for OO to read my mind and know what I want, nor is it to offer an obscure method like observe "Special Numbers". How is anyone supposed to know what "Special Numbers" are? The hidden apostrophe might well work if there was a simple option to remove them by "Find & replace" afterward. But OO doesn't offer any method to fix the OO-created problem. And therein lies the REAL problem. The only way for users to solve it is to keep asking the same question over and over, year after year.

edit this topic's initial post · Post by **Villeroy** » Mon Apr 07, 2014 8:20 pm

"Special Numbers" are explained when you hit the help button on that dialog.

Examples of special numbers:
1/2/12 (date, either Feb 1st or 2nd of January in 2012, depending on the cultural context)
12-1 (date, just like manual input. which date depends on the cultual context)
1. Januar (German date)
2014年1月1日 (same in Chinese)
12:23:59 time
TRUE (=1)
WAHR (same in German context)
false (=0)
13% (=0.13)
1.3e6 (=1.3 million in scientific notation)
1,3e6 (same with comma decimal)
not to mention thousands of currency symbols

And now for the corner cases:
1.234 (=1234 in all languages with point as thousands separator, else 1.234)
1,234 (=1234 in all languages with comma as thousands separator, else 1.234)
12.98 (English number but text in comma locales)
12,98 (comma decimal but text in English)
US dates imported with a non-US locale yield wrong dates with switched month and day, correct dates when month=day and text when month >12.

So all it takes when you import lines of text into Calc is:
1) The language (cultural background, unguessable)
2) the column separator
3) the text separator or {None}
4) if you want certain numeric expressions interpreted in the same way as keyboard input or not ("special numbers"). This is hard to understand if you know nothing about spreadsheets but this option had been introduced after massive complaints from noobs. Of course you can not satisfy the noobs without programming artificial intelligence.
The dialog remembers all the settings for subsequent text files to be opened.

Additional options which may be very helpful in some cases:
The text encoding.
The quoted numbers option (see online help).
You can define exceptions for each single column to be imported (may be hundreds) but you don't have to once you specified the general import options. Select preview columns (multiple selection is possible) and use the list box to specify an interpretation mode. Right-click on column headers gives the same options.
If you ignore the language and "special cells" option, this individual setup for each column should work anyway (of course people ignore all options including this one)

You must not import csv into spreadsheets if you are unable to handle any spreadsheets.
CSV is not a file format. It is mere convention about database tables exported to plain text files and imported back into database tables (row sets with strictly typed columns) when 2 databases can not talk to each other. In many cases, Base (or any other database tool) is by far more useful than a spreadsheet.
I think that Windows users who try to use Calc as a file opener for plain text should try this one instead: http://csved.sjfrancke.nl/ (15 MB download)

edit this topic's initial post · Post by **Villeroy** » Mon Apr 07, 2014 8:30 pm

All this would be so easy to answer and explain with a tiny snippet of example data.

Johnnydon · Post by **Johnnydon** » Sun Apr 20, 2014 12:31 pm

This works,
all the other garbage here does not.

Re: Apostrophe before value

Postby MTP » Thu Apr 03, 2014 9:37 pm
.................What does seem to work is highlighting the problem cells (it's OK if other cells are included, too, as long as it's OK for them to be numbers), go to Data -> Text to Columns, and hit 'OK'.

JJJoseph · Post by **JJJoseph** » Sun Apr 20, 2014 10:19 pm

Johnnydon wrote:This works, all the other garbage here does not.

Your suggestion not only doesn't work it has no relation to the original problem. The only solution that seems to work so far is user acnak's idea of using a regular expression search/replace. It's a bit of a reach for the average user (who may not even know what a regular expression is or does) but he gave enough information to make it work.

edit this topic's initial post · Post by **Villeroy** » Sun Apr 20, 2014 10:35 pm

I will not stop to reapeat it over and over again: The one and only key to the "problem" is actual data. Without knowing actual data, any discussion about text to number conversion is completely pointless.
If you simply would not import text the "problem" would not exist anyway.

JJJoseph · Post by **JJJoseph** » Sun Apr 20, 2014 11:03 pm

Villeroy wrote:I will not stop to reapeat it over and over again: The one and only key to the "problem" is actual data. Without knowing actual data, any discussion about text to number conversion is completely pointless.
If you simply would not import text the "problem" would not exist anyway.

PayPal, eBay, Amazon, and most banks send annual transaction summary reports in text (csv) format, for importing into spreadsheets. Excel can import the csv reports, and you can insist that anyone dealing with eBay, PayPal, Amazon, and most banks should be using Excel instead of OpenOffice, but that's not very encouraging for OpenOffice users. is it?

Post by **RusselB** » Mon Apr 21, 2014 12:34 am

Conditional that the entries are in columns, the following steps do work
For each column that has the dates in it, follow these steps.
1) Highlight the range of the dates.
2) Select Data -> Text to Columns
3) In the Field section, select the column
4) Change Column Type from Standard to Date (DMY)
5) Click OK

Johnnydon · Post by **Johnnydon** » Mon Apr 21, 2014 3:29 am

Hey JJJoseph,

if you look at the first entry, as I've provided for you here, you'll find the original problem is apostrophe's occuring before COLUMNS OF NUMBER'S
for one reason or another.

Martin.Flynn wrote:I've had problems with this before but haven't posted.
My problem is that I occasionally get an apostrophe in front of a number value throughout a column (or row) and then can't sum these values until I remove the apostrophe's cell by cell. Is there a way to remove all the apostrophe's in one go? I've tried reformatting the column/row but that doesn't work.

My suggestion took away the problem in one swoop and took me nearly 15sec to complete once found out what to do from MTP.

I cannot see anyone called anack on this thread, so your suggestion is a red herring, again.

As another contributor suggested, this worked but they used a command within the text to column options centre.

All I did was open it and click "OK"

if this doesn't work for you then you may have a problem that lies outside version 3.3

Post by **RusselB** » Mon Apr 21, 2014 3:58 am

To be fair, while Acknak hasn't posted in this topic, he has posted in other topics on the same subject, and that is where JJJoseph may be getting that reference from, thus the suggestion is not a red herring.

I also supplied the same suggestion that you made, which you stated you got from MTP.. with the slight difference in that my description seemed to be more detailed.

JJJoseph · Post by **JJJoseph** » Mon Apr 21, 2014 7:00 am

This discussion has been going on for years, and it seems to vary with different releases of OO Calc. The following is from about 4 years ago:
viewtopic.php?f=9&t=33971&p=156828&hili ... er#p156828

OO still hasn't mastered the csv import problem of the prepended apostrophe, but it seems to be getting there slowly. I've been using the regular expression solution to fix the numbers, and others seem to be using the Data -> Text to Columns. Whatever works, I guess.

edit this topic's initial post · Post by **Villeroy** » Mon Apr 21, 2014 11:09 am

I never get any unwanted text values when importing catalog data or bank accounts. What are concrete data from paypal, amazon etc? I don't communicate with any of these corporations.
So the answer remains the same: If you don't want the apostrophes before numeric expressions you must not import them as text. You should better import them as numbers. It is very easy to do. Easier than doing the same with Excel 2007: http://office.microsoft.com/en-us/excel ... 99725.aspx

Post by **RusselB** » Mon Apr 21, 2014 2:36 pm

It appears, based on what I've seen from other reports and my own experience, that OO doesn't always recognize dates as special numbers when the fields are generated for importing a CSV.
Most of the time, for a field that contains a date, OO shows the column type as Standard, rather than one of the Date options.
By changing this to the correct Date option before the actual CSV importation, I haven't had to go through any of the previously made suggestions.

edit this topic's initial post · Post by **Villeroy** » Mon Apr 21, 2014 3:15 pm

RusselB wrote:It appears, based on what I've seen from other reports and my own experience, that OO doesn't always recognize dates as special numbers when the fields are generated for importing a CSV.

Why that unspecific? Which dates are not recognized and which are? I always get all my own text data imported correctly. If you understand how it works you can import thousands of very different csv files with a few clicks on the right options.

Most of the time, for a field that contains a date, OO shows the column type as Standard, rather than one of the Date options.

100% of the fields to be imported are preset as standard fields. "Standard" means "according to the above options". Setting any column specific option is entirely up to you. Normally you don't have to.

By changing this to the correct Date option before the actual CSV importation, I haven't had to go through any of the previously made suggestions.

When you import dozends (or even hundreds) of text columns all you need to specify is (in order of importance):
1) Column delimiter
2) Locale (language)
3) "Special numbers"
4) Suppress evaluation of quoted strings or not.
5) Text encoding in rare cases.
When opening text files, these options are stored permanently in your configuration. Next time when you import similar files, you can simply confirm the dialog hitting the Enter key.

The individual column settings are useful to define exceptions to the rules. I did not need any of those since the locale option had been added to the text import dialog (OOo 3.3).

How to fix wrongly imported data within seconds:
1) Choose the right locale globally or in the cells number format options
2) Find&Replace regex .+ with &

How to import, edit, filter, sort and save csv without any trouble: http://csved.sjfrancke.nl/

JJJoseph · Post by **JJJoseph** » Tue Apr 22, 2014 12:21 am

Villeroy wrote:How to import, edit, filter, sort and save csv without any trouble: http://csved.sjfrancke.nl/

CSVEd is even more problematic than OO when dealing with csv files. And it gets into a knot and crashes completely when asked to do anything difficult.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 24, 2014 12:53 pm

Now we know that you have some instability on your system or problems with a certain text editor, but we still do not know which numeric data do import as text values.

JJJoseph · Post by **JJJoseph** » Thu Apr 24, 2014 10:56 pm

Villeroy wrote: we still do not know which numeric data do import as text values.

Villeroy, you've been present during the last few years as this topic is being discussed. I, and others have uploaded sample csv reports from PayPal, eBay, eTrade, etc. There's ample discussion of how to deal with this quirky Calc issue. Unfortunately, the new Calc user has to read through about 5 years of Q&A to get even a little understanding of how to deal with Calc's undocumented & quirky behavior.

Here's a sample of users struggling with the apostrophe-number for the last 5 years, and you've been present the entire time.

viewtopic.php?f=9&t=68299&p=305117&hili ... sv#p305117

viewtopic.php?f=9&t=67881&p=302429&hili ... sv#p302429

viewtopic.php?f=9&t=67791&p=302377&hili ... sv#p302377

viewtopic.php?f=9&t=67330&p=299707&hili ... sv#p299707

viewtopic.php?f=9&t=66520&p=296966&hili ... sv#p296966

viewtopic.php?f=9&t=55079&p=241345&hili ... sv#p241345

viewtopic.php?f=9&t=49046&p=224765&hili ... sv#p224765

viewtopic.php?f=9&t=46614&p=215721&hili ... sv#p215721

viewtopic.php?f=9&t=178&p=1083&hilit=ap ... +csv#p1083

User Acknack seems to have a good understanding and gives a good explanation. Look for his replies. The explanation isn't clear, but it gets easier if you patiently read through the last 5 years of replies. I don't know how the regex Find/Replace (explained by Acnak) works, but I'm happy enough that it does work and I can get on with my daily Calc chores.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 24, 2014 11:37 pm

None of your links demonstrates anything new. They demonstrate the need of concrete example data since csv is not a standardized file format. Wrongly imported data could be fixed by either find&replace or text-to-columns. Correct imports could be achieved by using import options locale and "special cells". The last topic shows that MS Excel is a bad reference when it comes to text import. It tries to treat bad data right which may fail with wrong values and no errors.
People complain when the program interpretes special numbers (before version 3.3), others complain when it doesn't but they do not use the new options (since version 3.3) to control the behaviour because they do not even understand how spreadsheets use to work since 3 decades.

edit this topic's initial post · Post by **Villeroy** » Fri Apr 25, 2014 12:30 am

How to aks and answer this type of question with a minimum of effort:

Q1: How do I get rid of the apostrophe before '$1.234 when importing text?
A1: Language = English(US) "Special Numbers"=checked

Q2: When importing text, some value 31/12/2013 is left aligned (text) whereas 2/4/2013 in the same column is right aligned (number).
A2: You imported with language setting English(US). The numeric value refers to the 4th of February and the other one is text because there is no 31th month. Use English(UK) instead.

Q3: When importing text, some value 31/12/2013 is left aligned (text) without apostrohpe whereas 2/4/2013 in the same column is left aligned with a preceeding apostrophe.
A3: You imported with language setting English(US) and without evaluation of "special numbers". The numeric value refers to the 4th of February and is tagged as text (the apostrophe) and the other one is text anyway because there is no 31th month. Import with English(UK) and "special numbers" option checked so all the dates will be interpreted in British context.

Q4: Why do I get left aligned text values with a preceeding apostrophe when importing text lines like the following: "1","20/12/2013","1.234"
A4: Uncheck "Quoted values as text", check the "special numbers option" and use English(UK) as import language if "1.234" means "one point two three four". If the point is meant to be a thousands separator you may use German as import language.

Only the example values reveal what you are talking about.

Neither the text-to-columns repair nor the find&replace trick would fix wrongly imported data in Q2 and Q3. You will get wrong dates unless you change the locale. The repair methods may also give wrong results for Q4 if 1.234 is meant to be an integer with thousands separator.

You mentioned csv reports from Amazon. They use MM/DD/YYYY when you call them from the US and DD/MM/YYYY otherwise. When you call a report for the first week of a month while OpenOffice assumes the wrong location, all the dates would be wrong if you ignore the cultural difference. When you call a report for the other weeks of a month, you will get a mixture of wrong dates (reverse month and day) and text values (month >12). The text values don't have any offending apostrophe because in the given context they are no valid dates anyway.

JJJoseph · Post by **JJJoseph** » Fri Apr 25, 2014 5:55 pm

Thanks for taking the time to check the references. None of this discussion has anything to do with calendar dates. Dates are simply another text item. All the queries listed were concerned about NUMBERS being fixed "permanently" as text. AFAIK, there is never any need to convert calendar dates to a date format; text dates are the preferred format for spreadsheets. Calc's "locale" parameter is really the source of all these problems. If it was up to me, I'd get rid of "locale" completely since it's badly implemented & more trouble than it's worth - or else allow dates to be specified as text.

Post by **Alex1** » Sat Apr 26, 2014 12:18 am

If you store dates as text, you cannot use them in calculations, you cannot change their appearance (e.g. to show the day of the week or change the number of year digits), and you cannot sort them, unless they are in year-month-day order.

[Solved] Apostrophe before value

[Solved] Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value (solved)

Re: Apostrophe before value (solved)

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re:Apostrophe before value (solved)

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value

Re: Apostrophe before value