[Solved] Apostrophe before value

[Villeroy]

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.

And you can not aggregate by time intervals while equivalent date strings do not yield the same cell value ("02/03/14" vs "2/3/2014").
Text import correctly imports data from the entire planet. is not exclusively made for your American Ebay,/PayPal/Amazon accounts although it does import them correctly. Amazon exports localized text files, by the way.

[JJJoseph]

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.

Nobody bothers with any of that in spreadsheets. And of course you can always sort dates in any medium as long as they're in ISO format (yyyy-mm-dd). Anybody doing calculations with dates (invoicing, for example) will have lots of choices in SQL functions. Dates are a non-problem with an easy solution. It's OO's "locale" that makes a mess of everything. If OO got rid of "locale", or we could completely turn it off, other difficulties like the hidden apostrophe would also disappear and we wouldn't have to go through all this blather year after year.

[Alex1]

Nobody bothers with any of that in spreadsheets.

Please don't generalize. How would you determine the day of the week of a date stored as text?

[RusselB]

How would I determine the day of the week of a date stored as text?.. First off I'd have to know what the text shows.
If I was lucky (not going to hold my breath on it, though), the day of the week would be included in the text.
If not, then I'd convert the text to date format using one of the many options that have been already posted, then set a custom format of DDD or DDDD

[Villeroy]

Nobody bothers with any of that in spreadsheets. And of course you can always sort dates in any medium as long as they're in ISO format (yyyy-mm-dd).

Nobody uses ISO format.

[RusselB]

I'll have to verify this, but I believe the payphones used by Bell Canada, or at least the ones in my area, uses that format (ISO) on their displays.

[Villeroy]

I'll have to verify this, but I believe the payphones used by Bell Canada, or at least the ones in my area, uses that format (ISO) on their displays.

It was just a reply to the statement that nobody bothers with dates in spreadsheets.
Although JJJoseph will never tell us anything about the data he allegedly can not process with Calc, I bet he's USAmerican.

[RusselB]

Probably a safe bet, as his location is stated as Vancouver, Canada (technically should be Vancouver, BC, Canada)

And my statement, was in reference to your reply of

Nobody uses ISO format.

[RoryOF]

I do, as far as possible. My system file dates and names incorporating dates are all ISO format.

[Martin.Flynn]

Oops! Hadn't checked back as I removed the apostrophes manually. Lots of info and "debate". The next time I have this problem I'll refer to the info in this thread and post results/examples.
Thanks to you all.

[Martin.Flynn]

Thanks Johnnydon, this does it perfectly. I've used your solution a few times now and it works every time. The problem was occurring when I copied and pasted from the web.

Thanks to you all.

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

[Villeroy]

Thanks Johnnydon, this does it perfectly. I've used your solution a few times now and it works every time. The problem was occurring when I copied and pasted from the web.

It happens to work for you every time. This method may fail under certain conditions.
Simply import your data as numbers rather than text so you don't have to correct wrongly imported data.

[Martin.Flynn]

It happens to work for you every time. This method may fail under certain conditions.
Simply import your data as numbers rather than text so you don't have to correct wrongly imported data.

I've tried this in a few different ways and find that dates set as D/M/Y and special numbers checked, for ID's, works for most of the data I'm copying. Telephone numbers depend on whether spaces have been entered on the form. The ones with spaces enter OK but those entered without, lose their leading zero, which are easy enough to reformat.
As ever, something is learned every time I use this forum.

Thanks Villeroy and everyone who contributed.

[Villeroy]

Phone "numbers" are no numbers at all. They are character sequences that happen to consist of digits due to the way how telephone switches used to be working back in the 20th century. Phone numbers and other identifiers should be imported as text anyway. You never calculate the sum, average, minimum, maximum nor any statistical analysis from the numeric values of identifiers.
When filtering and sorting identifiers you are not after the numerical value. You search for cells where some ID contains (starts with, ends with) some sequence, say "0123". Contrary to numbers where 0123 and 123 are the same, the text "0123" is supposed to be different from "123".

You may prefer to import numeric identifiers as numbers for the sake of pretty formatting but then you should be aware that the displayed character sequence differs from the actual cell value due to the way how spreadsheets were designed to be working back in the 20th century.

[jrkrideau]

Phone "numbers" are no numbers at all. They are character sequences that happen to consist of digits due to the way how telephone switches used to be working back in the 20th century. Phone numbers and other identifiers should be imported as text anyway..

Hear, hear, I tend to live in fear that some spreadsheet calculating a drug dose for me will use my telephone number rather than my weight. See the attached spreadsheet. I am pretty sure that Telephone Number * Telephone Number is meaningless but you can do it if you want.

[Kaz]

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

Yes Johnnydon is correct. I worked through one Columm at a time using " go to Data -> Text to Columns, and hit 'OK' "
And it fixed the 'nnn problem.
All of the other suggestions where a waste of time.
Good work Johnnydon

[Ari Abrams]

For me the search and replace did not work, but this worked great: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

Nobody intentionally adds those apostrophes and many of us are not computer programmers. If we copy and paste it should work. Answers to how to fix it should be focused on helping the customer only.

[Villeroy]

When you work with spreadsheets you are a programmer because spreadsheets are simplified programming languages.
The difference between the text value "00123" and the number 123 is essential in all programming languages. This is trivial and easy to understand. Any "Excel for Dummies" book teaches this differenciation.

You can easily import your text data correctly. It takes one or two clicks one time.
Fixing wrong data afterwards is more difficult and may give wrong data.

[cannonballdex]

I found the simplest solution to this somewhere and had to share it.

Linked data gives you ('$100.50) with leading apostrophe
=VALUE('linkeddata'.A3) gives you only the value.

[Villeroy]

I found the simplest solution to this somewhere and had to share it.

Linked data gives you ('$100.50) with leading apostrophe
=VALUE('linkeddata'.A3) gives you only the value.

The simplest solution is this formula applied to thousands of wrongly imported csv values?
Now do the same with 100,50€ which may occur in any csv file from the Euro zone.
Calc can handle any of these (even in the same file) if you do one or two more clicks.
When I import US data, I choose "English (USA)" as import language.
When I import German data, I choose "German (Germany)" as import language.
In the very rare case where 100,50€ and $100.50 occur in different columns of the same file, I choose German import language and mark the dollar column as "English (US)"

"Detect special numbers" should always be checked. You may turn it off for a few special cases. Whenever there is an apostrophe in front of a numeric expression, this tells us that this would be a number if it had not deliberately imported as a text value. "Deliberately imported as a text value" means that you did not check the option which lets the program interprete special numbers such as decimals with currency symbols. You did not check that option, so you do not get special numbers interpreted. SImple as that.