[SOLVED] How to remove apostrophe from all cells?

33 replies [Last post]
abcuser
Offline
Last seen: 2 years 6 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Hi,
I have got Excel file. First character in every cell in this document is apostrophe, so Calc hides this apostrophe and works with the cells like there are all characters. So I have a problem with numbers because there are recognized as characters. The work-around is to remove apostrophe manually from each cell like deleting this character from cell.

Is there any way I can delete all this apostrophe characters from all cells with simpler method?
Regards
mash
mash's picture
Offline
Last seen: 2 years 7 weeks ago
Title: ★★★★★
Joined: 17 Mar 2011
Posts: 196
As search and replace do not

As search and replace do not work here in calc I would recommend to copy the data to a notepad and search and replace there.


Before you copy and paste the data back into calc make sure you do not have set any options/formatting to interpret the data as text (otherwise you're again stuck with the same problem).

Paste it via <CTRL-SHIFT>-V and not without the Shift.
n/a
abcuser
Offline
Last seen: 2 years 6 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Mash, I have found out one
Mash,
I have found out one even simpler solution:
1. mark cells in first column
2. Data | Text to Columns
3. Text delimiter: '
4. repeat 1 to 3 for other columns

Is there any way I can remove apostrophe for a whole document. I have several columns and it is time consuming to do some actions from every column. I have found several solution for Excel using macro, but this macros are not working in Calc.

Any other idea?

Thanks
mash
mash's picture
Offline
Last seen: 2 years 7 weeks ago
Title: ★★★★★
Joined: 17 Mar 2011
Posts: 196
Record your action as a macro

Record your action as a macro and assign it to a shortcut.

After selecting the column in question you just press the shortcut.

n/a
Robert Tucker
Offline
Last seen: 1 year 27 weeks ago
Title: ★★★★★
Joined: 20 Feb 2011
Posts: 116
Search/replace

Search/replace (Ctrl+F)

Put a full stop (period) . in the Search for box.

Put an ampersand & in the Replace with box.

"More Options"

Check "Regular expressions"

Replace all.

abcuser
Offline
Last seen: 2 years 6 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Hi, I have opened new
Hi,
I have opened new document and inserted the following values:
Cell A1: '100
Cell A2: '100
Cell B1: '100
Cell B2: '100

<Ctrl>+F and did as you have instructed. This did not work for me. After I press "Replace All" button all of the values from cells A1, A2, B1 and B2 are deleted and cells get marked - see attached picture.

Have I done something wrong?
Thanks
AttachmentSize
find_replace.png 59.06 KB
mash
mash's picture
Offline
Last seen: 2 years 7 weeks ago
Title: ★★★★★
Joined: 17 Mar 2011
Posts: 196
@abcuser: select "Values" in

@abcuser: select "Values" in the combobox ß then it will work.

Thanks to Robert Tucker. Good idea.

n/a
abcuser
Offline
Last seen: 2 years 6 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Mash, yes if "Values" is
Mash,
yes if "Values" is selected then it works fine. I have also find out that "Backwards" option MUST be disabled. This is strange, looks like a bug to me. In help there is written: "Backwards Search starts at the current cursor position and goes backwards to the beginning of the file."

Robert, thanks a lot for this brilliant idea.
Regards
AttachmentSize
find_replace.png 23.46 KB
Jens S
Offline
Last seen: 12 hours 32 min ago
Title: ★★★
Joined: 26 Feb 2011
Posts: 32
If you use Search and

If you use Search and Replace, you should use:

Search for: .+  (search for any sign and 1 or more of the same, not just .)

Replace with; &

x  Regular expression


Don't change 'Search in' to 'Values'. It will destroy all of your working formulas in the sheet with the result of the formula.

abcuser
Offline
Last seen: 2 years 6 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Jens, I don't think there

Jens, I don't think there should be any character in Search box beside "." I have solved the problem, see attached picture at my previous post, how I have solved the problem. It looks like "Backwards" options has some kind of bug.

Fabimaru
Offline
Last seen: 2 years 9 weeks ago
Title:
Joined: 12 Jun 2011
Posts: 2
Additional step

Hi,

I had quite a similar problem. I imported a CSV file, and all the values became text values, the apostrophe being hidden. So what I had to do before applying Robert's  trick is to unhide this character. I had to change the format of the cells to number, but only for the cells containing actual data (so on everything but the first row which is the label). If I do that on the whole column, the apostrophe remains hidden.

n/a
Tinkerer
Offline
Last seen: 4 weeks 6 days ago
Title: ★★★★
Joined: 26 Mar 2011
Posts: 56
Remove the apostrophe.

There is a complicated thread to solve this problem.

The quick easy method is to use Cor Nouws extension CT2N.

Convert Text To Numbers.

You will find it in OOo Extensions, but hopefully, he will make it available in LO extensions.

n/a
EarlsFurniture
Offline
Last seen: 18 weeks 2 days ago
Title: ★★★★
Joined: 17 Feb 2011
Posts: 70
I had the same issue when importing a csv file.
The solution above is great if you already imported and made alterations to data you don't want to lose. But if you are still at a point to freshly import the data again, then do so, but this time make sure to choose the ' as a delimiter. The problem I had was ' is not a default selection for delimiters, so it gets put in as data which calc then uses to interpret the cell as text. Setting ' to a delimiter on import means calc strips it out of the field on import.
LSr
Offline
Last seen: 2 years 5 weeks ago
Title:
Joined: 11 Mar 2012
Posts: 1
Search box needs .+ (full stop and plus)

Hi,

it worked for me with adding a . (full stop) and the + (plus) in the search selection:

Search: .+

the rest is the same.


Otherwise, if I do not get answers here, I still search openoffice.org. It has a many solutions pre LibreOffice.



n/a
avenida6
Offline
Last seen: 1 year 36 weeks ago
Title:
Joined: 4 Aug 2012
Posts: 1
Easiest Way for ME

Just add 0 (zero) to the column containing those apostrophies and it will convert them to numbers.

A B

1 '123456 =A1+0

result

'123456 123456

Strange but it works.

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
works for me - worked for me too

Hi, that last tipe is great – quick and simple, though a little messy. I just made a hidden column to put my problem data in and then used my main coumn as that plus 0. I did try the addon but once installed I couldent find it anywhere (expcept to see that it was installed) – no time for that Smile x

lleyzpiercing
Offline
Last seen: 50 weeks 2 days ago
Title:
Joined: 2 May 2013
Posts: 1
used csv file format

i haven’t read all the replies or feedbacks on this thread but based on my experience, i download the file or save the document in csv format using excel or open office spreadsheets then have it open in a notepad. or one can also search and replace using the edit feature. well any way, this thread may have been resolved already due to the number of replies that i see. if not, hope you can update us about it too.

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
i disagree

Hey, I disagree that this post is solved, I think this is a real pain in the butt issue, probably a bug, totally unexplained and takes time to solve with some kind of ugly hack and that it needs to be delt with properly. Could it be a a bug report issue?

Best

Anna

oweng
oweng's picture
Online
Last seen: 4 min 10 sec ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2136
greater detail and example

I am happy to help try and resolve this, but it requires more detail of exactly what the problem is and preferable an example file. We appear to be dealing with an “Excel file”. Which format? XLS? XLSX? From which version of MS Office? On which platform? You mention Anna that it “takes time to solve with some kind of ugly hack”. What is the “ugly hack” that you refer to? Do you mean what is mentioned in posts #4, #7, and #8, up thread? This will help me help you.

As to the problem of using this regular expression … does it no longer work under the current v4.0 series? Evidently the cells in the “Excel file” appear to be prefixed with an “apostrophe”. Refer my answer here as to why this term is not descriptive enough and why using a regular expression to remove them may not work in some situations. If you can provide an example file for others to test it would help greatly.

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
Hey, so this was a while ago

Hey, so this was a while ago and I don’t remeber a lot of the stuff clearly now, however, in the next week I am due to export some data from my webshop, which is the task which caused this problem last time. I will feedback here what I know while I do that, and let you know what happens Smile I havent updated my shop but I have updated libreoffice btw, so we will see if this still occours.

Best

Anna

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
ok, so here it goes

I can’t export the data from my webshop as a csv so I have to copy and paste it. I do so with whatever pre-sets libreoffice uses, just ctrl+c and ctrl+v. At first all is fine, most of the data is numbers, eg 5 orders, 50 visitors. When I get to the last collumn, which is how much I sold, it all seems fine untill I try and add it up. It won’t add up, I get just the sum, or an error, or eventuallty just zero. I try removing the pound sign using find and replace but that dosn’t help, so I check my right click menu, look at cell format and discover the it is set as text. I set it to be a number. The numbers still won’t total. Eventually I notice there is an apostrophe in front of the number. I try find and replace the appostrope, but it says search key not found, and that takes me to where I was when I found this post origionally.

I try solution number 1. It dosn’t seem to work at first, as libreoffice wont let me paste special – however, I just paste as normal and it has worked.

Solution number two looks anoying and complicated, however, I guess I must have tried as I remeber the next part in soution 3, trying to learn macros (never got anywhere) I also recall trying the CT2N.extension, but once I installed it I spent hours looking for it and could never find where it was in the menus or menu options.

Anyway, does that help? I assume the issue I had was caused by pasting as text in some way, however, I can’t speak for the other people. And actually, its really casued by Prestashop being neo-proprietary junk and making basic features like exporting my own damn data to csv available only as paid addons. Bleugh!!

Best

Anna

oweng
oweng's picture
Online
Last seen: 4 min 10 sec ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2136
different issue

You are dealing with a different issue from the one original mentioned in this thread (Excel files being opened and having all cells with a leading apostrophe). This is a cut/paste data format issue. Calc does its best to handle receiving data via cut and paste, however it is limited by the markup of the source file. Again though you do not provide any real examples or detail.

… most of the data is numbers, eg 5 orders, 50 visitors.

This tells me nothing about the source file or data format. I cannot replicate this.

I try removing the pound sign …

This sound (to me) like you are cutting and pasting from a PDF or something equivalent, but again, you don’t say. Currency values should not have a currency sign, but I can understand that if you are doing a cut/paste from a report that they could include one. Any data containing a non-numeric will not add up as a matter of course. I can format a cell to display AUD$123.45 but the data in the cell needs to be 123.45.

This is why import via file (e.g., CSV or other delimited format) is far more efficient and less problematic. I asked several questions in my previous post which you have ignored. Until you start providing answers there is not much anyone can do to assist you.

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
Hey, sorry, I do understand

Hey, sorry, I do understand your frustration – I know almost nothing about spreadsheets, but I know what its like to be given irrelevant info endlessly while trying to help people in my own areas of know-how. I can only appologise that I can’t be much more helpfull, I really had done my best to give usefull infromation, however.

Anyway – the data I am pasting from a web page, and I see that the cause of my problem is not the same as the cause of the one in the current thread. I can imagine that there are other ways to cause this appostrope problem too. I get the impression that however it is achived, the problem of having appostropes in front of all the data is that same? So removing it could perhaps be done in the same way?

If this is the case, we could document the process of removing the apostrophes using whichever is the best method and we could explain some of the causes of the problem to help people avoid them. After all, even if the user is like me, and using Calc very badly and ineficently, they will still need to solve the problem to continue with their work. Its good for people to know why the problem is happending too, but I am not sure its always avoidable – for example, as I said, my website won’t produce a CSV file for me, I have to copy and paste the data.

Best

gingerling
Offline
Last seen: 48 weeks 5 days ago
Title:
Joined: 12 Oct 2012
Posts: 6
oweng wrote:On which

oweng wrote:
On which platform?

WIth this, I don’t know about other people, as you can see mine was not cause dby excell but by pasting in data. I am using fedora 18 with libreoffice Version 3.6.6.2. Origionally I was using Crunchbang, a variant of debian, with an older version of Libreoffice (can’t check it now)

oweng wrote:
You mention Anna that it “takes time to solve with some kind of ugly hack”. What is the “ugly hack” that you refer to? Do you mean what is mentioned in posts #4, #7, and #8, up thread?

Yes, I guess so. Most of the sugestions involve pasting into another app and back or using a work around such as creating a hidden coulmn or inputing some other sybols, rather than removing the apostophe.

oweng wrote:
If you can provide an example file for others to test it would help greatly.
I am not sure if my file would be of much value becuase its not from excel. Let me know if you still want one and I will make one for you Smile

Best

Anna

oweng
oweng's picture
Online
Last seen: 4 min 10 sec ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2136
That's OK. Step 1: data capture.

I am sorry if I sounded harsh Anna (I don’t mean to be), it is just that I can really only use the data you provide. Things like the format of the source and destination (saving) file are important, but not really as important (in your case) as data capture. This is helpful:

… the data I am pasting from a web page, and I see that the cause of my problem is not the same as the cause of the one in the current thread. I can imagine that there are other ways to cause this appostrope problem too. I get the impression that however it is achived, the problem of having appostropes in front of all the data is that same? So removing it could perhaps be done in the same way?

Pasting from a web page will generally use whatever markup is in the HTML. For data it is ideal if it is within a <table> element, but this is rarely the case. As an example I can highlight and copy the Top 10 list of exchange rates (just the 10 currency rows) against the GBP from here. When I come to paste that data into Calc I have a choice:

  • Retain the HTML formatting via CTRL+V but lose the calculation ability to work on the pasted values with functions like =SUM() OR
  • Lose the HTML formatting but retain the ability to work on the pasted values with functions like =SUM().

Generally what I want is the second option – unformatted text.

Two attached screenshots (x-rates_CTRL+V.png and x-rates_CTRL+SHIFT+V.png) show the corresponding dialogues (under v3.5.7.2, but the operation is similar under v4.0.2.2, you just don’t get the first dialog). If I then try and work on the two pasted ranges I quickly find that I can neither sum the “numeric” HTML values in the top array and they are unaffected by applying a currency (AUD) cell format (refer x-rate_AUD_HTML.png). The unformatted text not only has more options available when pasting to ensure recognition of values, the values can be both summed and have a currency (AUD) cell format applied (refer x-rate_AUD_data.png).

These are critical steps before you even get to saving the file a XLS or XLSX format and working out what works in that respect. Obviously any non-numeric character that is appended to a numeric is going to prevent it from operated on as a numeric. For example, of the three dates listed on this page under Last Major Eruption only the 1980 value is interpreted as a number. The values 1914-1917 and 1790s both include characters that prevent them, regardless of how they are pasted from being worked on. Thus, in this case the data has to be cleaned first.

I think what you were experiencing was HTML formatted content that simply refused any kind of editing. Always try and use unformatted text where possible. Let me know if this helps.

AttachmentSize
x-rates_CTRL+V.png 102.77 KB
x-rates_CTRL+SHIFT+V.png 152.43 KB
x-rates_AUD_HTML.png 134.25 KB
x-rates_AUD_data.png 135.35 KB
caliray
caliray's picture
Offline
Last seen: 6 weeks 2 days ago
Title: ★★★
Joined: 28 Jul 2013
Posts: 26
Worked like a charm

@Robert Tucker There were a lot of cells highlighted other than the cells with the troublesome numbers. Robert’s solution worked perfectly and is one for the Libreoffice tips and tricks file.

connorjones13
Offline
Last seen: 34 weeks 5 days ago
Title:
Joined: 19 Aug 2013
Posts: 1
Found a solution to my problem

My problem consisted of having an apostrophe(’) at the beginning of each cell. The cells were meant to be text and I needed to remove the apostrophe. I solved this by using the =Proper function on the cells. Then, just copy -> paste special -> paste value.

Not sure this is the solution for everyone’s problem, but hopefully it can help some of you out.

status1
Offline
Last seen: 22 weeks 6 days ago
Title:
Joined: 26 Oct 2013
Posts: 4
I have a similar problem

I have a similar problem although it’s not in every cell
I am importing a csv file produced by Quicken that contain fractions as prices so instead of 0.75 for example it would show 3/4
The problem is that some of these fractions after pasting it into a spreadsheet show up as text format and after I change the format to numbers instead of getting 0.75 I am still getting 3/4 but now there is an apostrophe’ in front of the number but only on the input line so the regular find and replace does not work in my case because it’s not actually in the cell it only shows on the input line

As soon as I remove the ‘ from the input line the number in the cell changes to 0.75
Is there a way to find and remove the ‘ from the input line or some other way that can remove this ghost or hidden apostrophe symbol ?

oweng
oweng's picture
Online
Last seen: 4 min 10 sec ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2136
fractions / up thread

Fractions are rather awkward to deal with and require a multi-step process to fix:

  1. Specify a fraction format for all the cells that contain data like 3/4 or '3/4 (Format > Cells… > select Category of “Fraction” and click OK.
  2. Use the find/replace regular expression given up thread in posts #4 or #8 i.e., Search for . OR .+ and Replace with &. Click Replace All.
  3. Now specify a decimal (Number with decimal places) format for all the cells that contain data like 3/4 (Format > Cells… > select Category of “Number”, set Decimal places to “3” (or as required) and click OK.
status1
Offline
Last seen: 22 weeks 6 days ago
Title:
Joined: 26 Oct 2013
Posts: 4
solved

I was able to solve the problem with the fractions quite easily now that I know how to do it
I was concentrating too much on the find and replace to get rid of the apostrophe instead of finding a way around it
After some searching I saw someone suggesting to multiply the fraction by 1
That got rid of the apostrophe and worked on fractions bigger than 1 like 1 1/2 but not for 3/4 because that would only give me a number that represents the date 3/4/13 so after some thinking I figured what if I make the fraction look like the 1 1/2 and it worked
So I used the concat. function to add a zero and a space in front of the 3/4
and that did the trick all the text fractions are now decimal numbers

status1
Offline
Last seen: 22 weeks 6 days ago
Title:
Joined: 26 Oct 2013
Posts: 4
Solved

After trying your suggestion that works even better since there are fewer steps but I am not sure how it works Perhaps you can explain in more detail
How does the regular expression work with the . and .+

I tried it with the . first but that didn’t work it just repeated the first character
but the .+ worked perfectly
Thanks for your help

oweng
oweng's picture
Online
Last seen: 4 min 10 sec ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2136
help page

The help page on regular expressions explains the two characters and provides examples. The addition sign implies one or more characters, with the full stop meaning any non-break character.

status1
Offline
Last seen: 22 weeks 6 days ago
Title:
Joined: 26 Oct 2013
Posts: 4
Thank you for the explanation

Thank you for the explanation because the link you showed only shows the individual symbols which don’t work by themselves It only works when they are combined
I clicked on the wiki link at the bottom under related topics then from the wiki page to this link
https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressio…

That link has the exact explanation of what it does combined with the & It strips the formatting basically getting rid of the apostrophe which is what I wanted to do in the first place and I guess everyone else who posted here
Thanks for your help

Ozyris
Offline
Last seen: 19 weeks 3 days ago
Title:
Joined: 4 Dec 2013
Posts: 1
Removing the Apostrophes

Here’s what I did:

I had a whole matrix of data in rows and columns imported into Calc which had apostrophes and some columns had formulas.

I created a new blank sheet ‘Sheet2’. I put the following formula in cell A1 of the new Sheet2.


=VALUE('Sheet1'.A1)

‘Sheet1’ is actually whatever I called the original sheet. Then I copied this formula so that all the values of the cells with the apostrophes in Sheet1 showed up on Sheet2 in their matching cells.

Then I simply copied the values from Sheet2 back to Sheet1 and pasted them as ‘Special’ and made sure only “numbers” were ticked.

Simples.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.