Page 1 of 2
[Solved] Delete apostrophe before numbers in all cells?
Posted: Fri Mar 16, 2012 4:38 am
by Kingrazor
I have a spread sheet where the numbers all have apostrophes in front of the numbers to make them be treated as text and not as numbers. Is there a way I can get rid off all of them? Or will I just have to go line by line and delete the apostrophes?
Re: Delete apostrophy before numbers in all cells?
Posted: Fri Mar 16, 2012 4:54 am
by FJCC
The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text. To reenter all the data as number:
1. Highlight all of the cells and use Format -> Cells to change the cell format to an appropriate number format.
2. With all of the cells still selected, go to the menu Edit -> Find & Replace
3. In the Search For box enter .* (period asterisk)
4. In the Replace with box enter &
5. Select More Options and check Current Selection Only and Regular Expressions
6. Click Replace All
The .* "means zero or more of any character" and & means "whatever was found". These are regular expressions which are explained in the help section
Re: Delete apostrophy before numbers in all cells?
Posted: Fri Mar 16, 2012 6:40 am
by Kingrazor
Thanks!
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Dec 19, 2013 9:42 am
by yuraf
Just registered to say thanks
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Dec 19, 2013 5:53 pm
by Villeroy
FJCC wrote:
The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text.
The apostrophe isn't really part of the cell content, it just signifies that the cell is
formatted as text. It is not formatted as text. It
is text. If it were formatted as text, the apostrophe would not be there.
It would be easier to import those values correctly rather than fixing the problem afterwards.
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Jan 02, 2014 6:40 pm
by DeniseKaplan
Hi...Am receiving '$12345.67 when importing Charles Schwab exported data in csv format into OpenOffice Calc version 4.0.1 AOO401m5(Build:9714) - Rev. 1524958
2013-09-20 11:40:29 (Fr, 20 Sep 2013) on Windows 8 touch screen operating system...
Executed .* to find the '$##### in selected rows and used & (ampersand-shift 7) as replacement character....resulting in cell's content being deleted... any ideas what I could be doing wrong?? Tried to record macro to deleted the ' which did not record easily and therefore resorted to google search leading me to this posting...
Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Thanks in advance.
Denise.
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Jan 02, 2014 8:04 pm
by Villeroy
DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Do not ignore the import options.
Import with US English language and check "special numbers".
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Jan 02, 2014 8:23 pm
by DeniseKaplan
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sun Apr 05, 2015 5:14 am
by RobGreen
If you have a sheet with lots of already imported cells with the dreaded apostrophe, Paste Special as RTF individual columns into Wordpad; copy those columns from WordPad and Paste Special back into OO Calc as Formatted Text - hey presto, all figues back into place complete with any blank cells in the column, without apostrophes.
RG
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Tue Apr 28, 2015 2:49 am
by litterbug
Problem solved--another thread described the same problem and gave the solution--setting the "character set" to Unicode (UTF-8). All is well, and next time I'll do a better search before duplicating someone else's issue.
(see additional info at bottom)
Disaster!! I seem to have broken NeoOffice completely!!
I'd already found that the find/replace method works to remove apostrophes, but because changing the import settings to US English and checking the special numbers box sounded easier I decided to try it. I must have done something terrible, because now my import window shows a line of Asian characters (to my uneducated eye it looks like Japanese kanji) where the preview of the spreadsheet should be when I try to open a CSV (Excel) file. I've tried changing the settings back but I must be missing something because nothing seems to work.
Here are the current import settings:
Character set: Unicode
Language: English (USA)
Separator options: Tab and Comma
Other options: nothing checked
Fields: Column type: US English
Does anyone know how to fix this? Is it the settings? Should I uninstall and reinstall NeoOffice? Or is there a NeoOffice preferences file I could delete or reset?
Additional info:
I'm running NeoOffice 3.4.1 patch 14 on an iMac with OSX 10.9.5 . Also, NeoOffice opens a blank window and freezes if I hit "OK" on the text import page described above, and I have to force-quit the program to get out.
Re: Delete apostrophy before numbers in all cells?
Posted: Tue Dec 22, 2015 4:07 pm
by estatistics
FJCC wrote:The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text. To reenter all the data as number:
1. Highlight all of the cells and use Format -> Cells to change the cell format to an appropriate number format.
2. With all of the cells still selected, go to the menu Edit -> Find & Replace
3. In the Search For box enter .* (period asterisk)
4. In the Replace with box enter &
5. Select More Options and check Current Selection Only and Regular Expressions
Step 5.5 Select More Options menu, set on "Search in" the option "Values" NOT "formulas" as it is the default
6. Click Replace All
The .* "means zero or more of any character" and & means "whatever was found". These are regular expressions which are explained in the help section
For myopenoffice version, what worked was to SELECT the columns/row of interest AND then to apply, all the other steps! For some reason, "converting to numbers" dont work for me!
Re: Delete apostrophy before numbers in all cells?
Posted: Tue Feb 21, 2017 11:21 pm
by retlaw704
Just want to leave a note for FJCC.
I was having this problem many times. His suggestion was right on. It worked as he said it would.
I also liked that he explained what he did.
Thanks,
Walter
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Thu Nov 09, 2017 12:26 pm
by CrazyDaisy
Just registered too to say THANKS FJCC! Problem solved. As to the slightly pedantic "It would be easier to import those values correctly rather than fixing the problem afterwards.", if any of us arriving on this page thanks to FJCC's solution _could_ have done this and many still would if they could, and they should if they can, I know I'm trying to man... we wouldn't be making the effort to find a workaround in the meantime. Data gots to be gets processed you know
Thx again FJCC
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sat Dec 09, 2017 5:08 pm
by AlwaysPaws
To FJCC - it's five years later and I needed this today! Thanks very much for the solution.
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sat Dec 09, 2017 8:46 pm
by Villeroy
AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution.
You could also use the correct import options to avoid the whole problem.
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sun Dec 10, 2017 12:12 pm
by CrazyDaisy
Villeroy wrote:AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution.
You could also use the correct import options to avoid the whole problem.
I am beginning to think Villeroy may be a bot. If any of us did know how to do what you suggest, we would never arrive here in the first place... Or maybe our workflow doesn't make it easy or possible... Care to outline how we go about setting the correct import options? Now that would be helpful
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sun Dec 10, 2017 1:14 pm
by robleyd
Villeroy wrote:DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Do not ignore the import options.
Import with US English language and check "special numbers".
Villeroy provided the answer above when he said about special numbers
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sun Dec 10, 2017 3:57 pm
by Villeroy
robleyd wrote:Villeroy wrote:DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Do not ignore the import options.
Import with US English language and check "special numbers".
Villeroy provided the answer above when he said about special numbers
which is one 258 posts where I mentioned "special" and "numbers":
search.php?keywords=%2Bspecial+%2Bnumbe ... mit=Search
Indeed, I feel like a bot. But what should we do if people are incapable of searching for solutions? Stop answering?
590 matches of "apostrophe" in the Calc forum
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Sun Jan 07, 2018 8:50 pm
by Rod Henrickson
I don't know about importing, but I had the exact same problem with copying and pasting bank records into Open Office spreadsheets. Was maddening as hell until some trial and error. I solved it by copying in the usual manor. Then selecting (Paste special). Then choose (Unicode) and on the following screen check the (Detect special numbers) box. The numbers were then pasted without the apostrophe in front which treats them as text and won't allow you to use them. This problem just popped up this year 2018-01-07. Never had this problem before that.
stupid shit by
Rod Henrickson, on Flickr
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Mon Jan 08, 2018 2:55 pm
by Villeroy
Rod Henrickson wrote:I don't know about importing,
You imported a string of characters through the clipboard. See caption of the dialog which reads "Text Import".
In this topic which has been marked as [Solved] and viewed 77,000 times and in hundreds of similar topics "special numbers" has been recommended.
search.php?keywords=%22special+numbers% ... mit=Search
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Tue Apr 03, 2018 6:44 pm
by WOC1996
Fjcc method does not work.
I have an excel export from a website with the numbers formatted as text. And I don't ever get an import dialogue, and having spent 2 1/2 hours looking for an import dialoge I give up. Just how in the hell can I get the numbers converted.?
opening the file, it just opens..... setting the load/save options does nothing.. just where in the hell is this import dialogue? copy/paste special ...nothing...
text to columns is only thing that works...
would be nice to import the damn thing fixed.
Re: [Solved] Delete apostrophy before numbers in all cells?
Posted: Tue Apr 03, 2018 7:38 pm
by Villeroy
YOU MUST NOT IMPORT WRONG DATA AND THEN TRY TO FIX THEM AFTERWARDS.
Just check the right language and the "Special Numbers" option and you are done.
1. LANGUAGE = English(UK) or English (USA) or some European langauage in most cases
2. SPECIAL NUMBERS = always ON
(Solved)converting text to numbers, another solution
Posted: Fri May 17, 2019 1:11 pm
by Murti
Downloaded a CSV file which opened in Open Office 4.1.3 on a Mac OS 10.10.5. It wasn't imported so I didn't have any options on how it opened. Columns showing currency figures wouldn't add up. Looked on here and found a solution to Find and Replace .* with &. It worked for some but this didn't work for me, (maybe a Mac thing?). I'm not a computer expert at all but just thought I'd try Find and Replace a £ for a £. It worked! Obviously the columns have to be displaying the £ or it wont work but that part can be done with the formatting.
It was a fluke, but I thought I would share it.
Re: (Solved)converting text to numbers, another solution
Posted: Sat May 18, 2019 10:46 am
by Villeroy
Murti wrote:Downloaded a CSV file which opened in Open Office 4.1.3 on a Mac OS 10.10.5. It wasn't imported so I didn't have any options on how it opened. Columns showing currency figures wouldn't add up. Looked on here and found a solution to Find and Replace .* with &. It worked for some but this didn't work for me, (maybe a Mac thing?). I'm not a computer expert at all but just thought I'd try Find and Replace a £ for a £. It worked! Obviously the columns have to be displaying the £ or it wont work but that part can be done with the formatting.
It was a fluke, but I thought I would share it.
If the file opens wthout text import dialog it might be an ordinary Excel file with a misleading .csv name extension but full of text values. The incompetence is not on your side.
When the file is visible in OpenOffice, menu:File>Properties, tab "General" tells you everything about the file and the actual type of file.
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Sat May 18, 2019 1:41 pm
by Murti
Actually the file did open with the Import dialog, and I can see the how checking the special number box works too. I am not used to the term 'Importing' when merely opening a file. 'Importing' is something I've always done from the file menu. Thanks anyway, it's all good experience
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Sat May 18, 2019 9:18 pm
by Villeroy
There are thousands of different flavours of csv out there. Csv is plain text. Valid csv is a database exchange format. It has not a single attribute that makes it compatible with spreadsheets, no formulas, no formatting attributes. nothing but lines of plain text separated by one or another column separator, with or without quoted text, with or without column labels, in some text encoding or another- There may be even cultural idiosyncrasies such as decimal notations, date notations, currencies.
The one and only answer to this whole f***ng topic is always the same: Do not ignore import options and complain afterwards about the complexity to fix wrongly imported data!
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Fri Dec 06, 2019 6:58 am
by npandit
IS there any code to avoid apostrophe before numbers?
Cant Format the cells always
Is there any way to do it through coding?
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Fri Dec 06, 2019 10:15 am
by Zizi64
I just can quote Villeroy's best solution:
The one and only answer to this whole f***ng topic is always the same: Do not ignore import options and complain afterwards about the complexity to fix wrongly imported data!
Is there any way to do it through coding?
Do you mean a macro code?
Yes there is.
But it is depend on the structure and complexity of the data range on a sheet.
You can detect the type (string, numeric, formula) of the cells by a macro code, and then you can do a "cut/paste special as unformatted text" by the code. The Calc will recognise the numeric format.
And then you can restore the applied cell style, what has been resetted to the Default by the command "cut"
Or you can get the cell content as text, and then you must convert it to numeric type, finally you need set the content as cell.value... in this case not needed manage the formatting properties. But you MUST know the cells where the procedure must apply or not.
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Sat Dec 07, 2019 9:11 pm
by Villeroy
npandit wrote:IS there any code to avoid apostrophe before numbers?
Cant Format the cells always
Is there any way to do it through coding?
Stop typing the apostrophe before numbers.
Stop importing numbers as text.
When importing from text file or textual clipboard content, check option "detect special numbers" even if you don't know what that means.
Has been answered many times in this thread alone.
Re: [Solved] Delete apostrophe before numbers in all cells?
Posted: Wed Oct 07, 2020 10:53 am
by Ari Abrams
Rod Henrickson is the only one who wrote an understandable reply. Shame the other members of the forum cannot write for people who do not already know the answers.