[Solved] Removing single quote from numbers

Discuss the spreadsheet application

[Solved] Removing single quote from numbers

Postby gardener41 » Fri Aug 11, 2017 9:00 pm

Somehow when I imported some data, a single quote mark was placed in front of the numbers so they appear to be text instead of numbers. How do I remove the quote on a mass scale. (editing each cell would take forever!)
Last edited by Hagar Delest on Thu Dec 13, 2018 9:04 am, edited 1 time in total.
Reason: tagged solved
David Gardener
OOO3.2.1
OOO320m18 (Buildi9502)
gardener41
 
Posts: 3
Joined: Fri Feb 25, 2011 4:58 am

Re: Removing single quote from numbers

Postby acknak » Fri Aug 11, 2017 9:14 pm

You can remove the quote with Edit > Find & Replace

You can also configure the text import to handle the conversion. The usual culprit is a CSV/text file that has double quotes around every field. OO Calc normally takes double quotes to indicate text values; text that could be interpreted as a number is displayed with the quote prefix to show the text value type and prevent the text from being converted to a number. If you turn off the "Quoted field as text" option, OO Calc should import the numbers directly as numbers even if they have quotes around them.

If that doesn't help, or you want some more specific suggestions, we'll need to have a sample of the text data you're working with.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Removing single quote from numbers

Postby gardener41 » Fri Aug 11, 2017 10:52 pm

I tried to FIND with '
and REPLACE with a blank space
It gave me SEARCH KEY NOT FOUND
David Gardener
OOO3.2.1
OOO320m18 (Buildi9502)
gardener41
 
Posts: 3
Joined: Fri Feb 25, 2011 4:58 am

Re: Removing single quote from numbers

Postby acknak » Fri Aug 11, 2017 11:54 pm

Sorry, it's a bit more complicated than I remembered.

1) Select the cells you want to change
2) Make sure the cell format is NOT text: Format > Cells > Numbers > Category: Numbers, Format: General (any numeric format will work)
3) Edit > Find & Replace
Search for: .* (that's two characters: period, asterisk)
Replace with: &
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Removing single quote from numbers

Postby erbsenzahl » Sat Aug 12, 2017 7:54 am

gardener41 wrote:Somehow when I imported some data, a single quote mark was placed in front of the numbers so they appear to be text instead of numbers.
If your data are in column order so use:
Data menu > Text to columns
The easiest way to get rid of the single quote marks.

Sometimes the following proceeding works: Cut your data out of the sheet and insert as unformatted or formatted text (use arrow/triangle beside the paste icon).

Or proceed with acknak's tips, they work great.
OpenOffice 4.1.6 on LinuxMint 17/18-64 Mate
(author is no native speaker)
erbsenzahl
Volunteer
 
Posts: 134
Joined: Tue Apr 18, 2017 8:23 am
Location: Germany

Re: Removing single quote from numbers

Postby Villeroy » Sat Aug 12, 2017 10:38 am

It's always preferable to import numbers correctly. Always turn on the "special numbers" option.
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: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Removing single quote from numbers

Postby anzon » Wed Dec 12, 2018 2:56 pm

YES IN BIG LETTERS FOR PEOPLE LIKE ME

TEXT TO COLUMNS

MANY, MANY THANKS

I think a lot of people have been getting the "leading-apostrophe-itis" problem and causing themselves and respondents a lot of unnecessary care and woe.
Could the OO help section have the "leading apostrophe" as a suggestion on its own page?
open office 3.1.1 OOO310M19(BUILD:9420)
windows xp
anzon
 
Posts: 5
Joined: Wed Mar 24, 2010 2:56 pm

Re: Removing single quote from numbers

Postby Villeroy » Wed Dec 12, 2018 5:38 pm

This is the most basic spreadsheet knowledge since the 80ies. Every old Excel book describes the difference between the number 123 and the text "123" within the first chapters. This is essential to know before you start editing a spreadsheet document.
Using the find tab in the F1-help, "apostrophe" finds 3 relevant chapters mentioning the apostrophe in the context of numeric text.
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: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

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