[Solved] Delete apostrophe before numbers in all cells?

Discuss the spreadsheet application
Kingrazor
Posts: 7
Joined: Wed Jan 04, 2012 8:55 am

[Solved] Delete apostrophe before numbers in all cells?

Post 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?
Last edited by Hagar Delest on Fri May 17, 2019 9:38 pm, edited 2 times in total.
Reason: tagged [Solved].
OpenOffice 3.3 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delete apostrophy before numbers in all cells?

Post 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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Kingrazor
Posts: 7
Joined: Wed Jan 04, 2012 8:55 am

Re: Delete apostrophy before numbers in all cells?

Post by Kingrazor »

Thanks!
OpenOffice 3.3 on Windows XP
yuraf
Posts: 1
Joined: Thu Dec 19, 2013 9:38 am

Re: [Solved] Delete apostrophy before numbers in all cells?

Post by yuraf »

Just registered to say thanks :)
OpenOffice 4.0.1 on Windows 7 Ultimate
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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.
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
DeniseKaplan
Posts: 4
Joined: Thu Jan 02, 2014 6:21 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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.
Windows 8 with touch screen, Open office 4.0.1 AOO401m5(Build:9714) - Rev. 1524958
2013-09-20 11:40:29 (Fr, 20 Sep 2013
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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".
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
DeniseKaplan
Posts: 4
Joined: Thu Jan 02, 2014 6:21 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post by DeniseKaplan »

:D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D

Many Many Many Many Thanks.....English USA AND Detect Special Numbers DOES THE TRICK!!!! So much future time to be saved....<click><backspace><enter-key> no longer in my future!!

:D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D

Happy New Year!!
Windows 8 with touch screen, Open office 4.0.1 AOO401m5(Build:9714) - Rev. 1524958
2013-09-20 11:40:29 (Fr, 20 Sep 2013
RobGreen
Posts: 1
Joined: Sun Apr 05, 2015 5:02 am

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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
Apache Open Office 4.0.1 Windows 7
litterbug
Posts: 2
Joined: Tue Apr 28, 2015 2:29 am

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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.
OpenOffice 3.4.1 on Mac OS 10.9.5
User avatar
estatistics
Posts: 25
Joined: Tue Oct 13, 2015 8:13 pm
Location: worldwide

Re: Delete apostrophy before numbers in all cells?

Post 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!
OpenOffice 4.2.8.2 on Lubuntu
User avatar
retlaw704
Posts: 3
Joined: Tue Sep 14, 2010 10:29 pm

Re: Delete apostrophy before numbers in all cells?

Post 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

:D :bravo:
openoffice 3.2.1 on windows xp
CrazyDaisy
Posts: 2
Joined: Thu Nov 09, 2017 12:16 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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 :fist:
OpenOffice 4.1.3 Windows 10 (ugh!)
AlwaysPaws
Posts: 6
Joined: Fri Feb 12, 2016 5:16 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post by AlwaysPaws »

To FJCC - it's five years later and I needed this today! Thanks very much for the solution. :bravo:
Apache OpenOffice 4.1.2
OS X Yosemite version 10.10.5 (desktop)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post by Villeroy »

AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution. :bravo:
You could also use the correct import options to avoid the whole problem.
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
CrazyDaisy
Posts: 2
Joined: Thu Nov 09, 2017 12:16 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post by CrazyDaisy »

Villeroy wrote:
AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution. :bravo:
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 :geek:
OpenOffice 4.1.3 Windows 10 (ugh!)
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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
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
Rod Henrickson
Posts: 1
Joined: Sun Jan 07, 2018 8:37 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

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


Imagestupid shit by Rod Henrickson, on Flickr
OpenOffice 4.1.3. windows
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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
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
WOC1996
Posts: 1
Joined: Tue Apr 03, 2018 6:23 pm

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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.
OpenOffice 4.1.1 on win10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophy before numbers in all cells?

Post 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
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
Murti
Posts: 2
Joined: Fri May 17, 2019 12:48 pm

(Solved)converting text to numbers, another solution

Post 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. :-)
Open Office 4.1.3
OSX Yosmonite
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: (Solved)converting text to numbers, another solution

Post 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.
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
Murti
Posts: 2
Joined: Fri May 17, 2019 12:48 pm

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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 :-)
Open Office 4.1.3
OSX Yosmonite
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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!
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
npandit
Posts: 16
Joined: Mon Nov 11, 2019 10:09 am

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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?
Open Office 4.1.7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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.
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
Ari Abrams
Posts: 8
Joined: Mon Jul 25, 2011 9:12 am

Re: [Solved] Delete apostrophe before numbers in all cells?

Post 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.
OpenOffice 2.4 on Ubuntu 9.04
Locked