[Solved] Delete apostrophe before numbers in all cells?

Discuss the spreadsheet application

[Solved] Delete apostrophe before numbers in all cells?

Postby Kingrazor » Fri Mar 16, 2012 4:38 am

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
Kingrazor
 
Posts: 7
Joined: Wed Jan 04, 2012 8:55 am

Re: Delete apostrophy before numbers in all cells?

Postby FJCC » Fri Mar 16, 2012 4:54 am

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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delete apostrophy before numbers in all cells?

Postby Kingrazor » Fri Mar 16, 2012 6:40 am

Thanks!
OpenOffice 3.3 on Windows XP
Kingrazor
 
Posts: 7
Joined: Wed Jan 04, 2012 8:55 am

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

Postby yuraf » Thu Dec 19, 2013 9:42 am

Just registered to say thanks :)
OpenOffice 4.0.1 on Windows 7 Ultimate
yuraf
 
Posts: 1
Joined: Thu Dec 19, 2013 9:38 am

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

Postby Villeroy » Thu Dec 19, 2013 5:53 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby DeniseKaplan » Thu Jan 02, 2014 6:40 pm

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

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

Postby Villeroy » Thu Jan 02, 2014 8:04 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby DeniseKaplan » Thu Jan 02, 2014 8:23 pm

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

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

Postby RobGreen » Sun Apr 05, 2015 5:14 am

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
RobGreen
 
Posts: 1
Joined: Sun Apr 05, 2015 5:02 am

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

Postby litterbug » Tue Apr 28, 2015 2:49 am

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
litterbug
 
Posts: 2
Joined: Tue Apr 28, 2015 2:29 am

Re: Delete apostrophy before numbers in all cells?

Postby estatistics » Tue Dec 22, 2015 4:07 pm

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
estatistics
 
Posts: 10
Joined: Tue Oct 13, 2015 8:13 pm
Location: worldwide

Re: Delete apostrophy before numbers in all cells?

Postby retlaw704 » Tue Feb 21, 2017 11:21 pm

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
User avatar
retlaw704
 
Posts: 3
Joined: Tue Sep 14, 2010 10:29 pm

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

Postby CrazyDaisy » Thu Nov 09, 2017 12:26 pm

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!)
CrazyDaisy
 
Posts: 2
Joined: Thu Nov 09, 2017 12:16 pm

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

Postby AlwaysPaws » Sat Dec 09, 2017 5:08 pm

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)
AlwaysPaws
 
Posts: 6
Joined: Fri Feb 12, 2016 5:16 pm

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

Postby Villeroy » Sat Dec 09, 2017 8:46 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby CrazyDaisy » Sun Dec 10, 2017 12:12 pm

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!)
CrazyDaisy
 
Posts: 2
Joined: Thu Nov 09, 2017 12:16 pm

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

Postby robleyd » Sun Dec 10, 2017 1:14 pm

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
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2886
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby Villeroy » Sun Dec 10, 2017 3:57 pm

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": https://forum.openoffice.org/en/forum/s ... 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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Rod Henrickson » Sun Jan 07, 2018 8:50 pm

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

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

Postby Villeroy » Mon Jan 08, 2018 2:55 pm

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.
https://forum.openoffice.org/en/forum/s ... 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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby WOC1996 » Tue Apr 03, 2018 6:44 pm

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

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

Postby Villeroy » Tue Apr 03, 2018 7:38 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

(Solved)converting text to numbers, another solution

Postby Murti » Fri May 17, 2019 1:11 pm

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

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

Postby Villeroy » Sat May 18, 2019 10:46 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Murti » Sat May 18, 2019 1:41 pm

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

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

Postby Villeroy » Sat May 18, 2019 9:18 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 23 guests