Clearing formatting.

users
#1

Hi, just wondering if there is an easy way to clear formatting of the style '2892 where the apostrophe is used.
When I paste data from a web page LO inserts the apostrophe by default. I realise there are options on pasting to have numbers pasted as text, but my query is in regards converting '2892 to the number 2892.

I cannot Format Cells... format as number or clear direct formatting or find and replace the '. I can Ctl-x, Ctl-Shft-v and paste and the ' is cleared.

Steve

#2

=On 6 Apr 2022 at 10:13, Steve Edmonds wrote:

Date sent:60;Wed, 6 Apr 2022 10:13:51 +1200

#3

...just wondering if there is an easy way to clear formatting of the style '2892 where the apostrophe is used. When I paste data from a web page LO inserts the apostrophe by default. I realise there are options on pasting to have numbers pasted as text, but my query is in regards converting '2892 to the number 2892.

Are we talking spreadsheets? I don't think you mean formatting at all. At least, I suspect those apostrophes do not appear in the spreadsheet cells but instead only in the Input Line when you select a relevant cell. The apostrophe is just an indication - a warning - that what looks like a number in the cell is actually text. (Another indication may be that the values are left- rather than right-aligned by default.)

I cannot Format Cells... format as number or clear direct formatting ...

Well, you can do all of those things, but none of them will change the values you have in the cells, which is what you need to do - to convert text to the numerical value of that text, to convert two-eight-nine-two to two thousand, eight hundred and ninety-two.

... or find and replace the '.

You certainly cannot do that, as there is no apostrophe in the cell value to be found!

Here's an easy way:
o Select the column of values.
o Go to Data | Text to Columns... .
o OK.

I trust this helps.

Brian Barker

#4

...just wondering if there is an easy way to clear formatting of the style '2892 where the apostrophe is used. When I paste data from a web page LO inserts the apostrophe by default. I realise there are options on pasting to have numbers pasted as text, but my query is in regards converting '2892 to the number 2892.

Are we talking spreadsheets? I don't think you mean formatting at all. At least, I suspect those apostrophes do not appear in the spreadsheet cells but instead only in the Input Line when you select a relevant cell. The apostrophe is just an indication - a warning - that what looks like a number in the cell is actually text. (Another indication may be that the values are left- rather than right-aligned by default.)

I cannot Format Cells... format as number or clear direct formatting ...

Well, you can do all of those things, but none of them will change the values you have in the cells, which is what you need to do - to convert text to the numerical value of that text, to convert two-eight-nine-two to two thousand, eight hundred and ninety-two.

... or find and replace the '.

You certainly cannot do that, as there is no apostrophe in the cell value to be found!

Here's an easy way:
o Select the column of values.
o Go to Data | Text to Columns... .
o OK.

I trust this helps.

Thanks Brian, the above worked.
Steve

#5

Note: That the "`" does work. In my testing I must have
enter ' instead of ` and the is why the "`" didn't work
since it was char(39) instead of char(96)..

Just wondering if the data is from a public page or some
kind of private page?? I have a spreadsheet that uses the
external data link to a web site to get the latest covid
stats for the US states, D.C. and territories. Than have
macro that processes it into rest of sheet.

Would be interesting to see how the spreadsheet
processes in the ` with the number. When I type in a
number with a ` at the front, it shows that value as a text
field and is left justified..

<msetzerii@gmail.com>
<steve.edmonds@ptglobal.com>,
               users@global.libreoffice.org
Date sent: Wed, 06 Apr 2022 10:58:56 +1000
Send reply to: msetzerii@gmail.com
Priority: normal

#6

The data is from part of a table on a public page but created in response to entering a street address.
There are no issues when I paste the data if I select "detect special numbers" and there are no issues processing the numbers preceded with a '  in a calculation.

I started wondering why LO doesn't see '123 as a format it can change or is it an over riding format that LO won't change and when I have a format like '123 how do I get it back to a number. If I type '123 into a cell, Format>Cells.. tells me it is a number, I can use it in a formula but can't sum it.

If you format a cell as text (right click or Format>Cells..) and type in $123.00 you have some text as expected. If you delete the $ you have 123.00. If you now Format>Cells.. and select Number>General you would expect this to become a number format, but LO inserts the ' so it becomes '123.00 and changes the cell format to number but forcing the contents to remain as text.

From this point my question arose as to how to remove the ' so the contents of the cell became a number, and which Brian solved for me.

Steve

#7

=Recall in Lotus and Quattro Pro you could enter for text
fields a single quote, double quote or carat that would set
cell alignment to left/right/center.
Libreoffice doesn't seem to support that, and have to
manually set alignment.

Tried entering numbers directly in calc and got these
results.

A</span>B =a1+1
123<span style="display:inline-block; font-size:1pt; width:34.000000pt">0;124straight 123Right aligned</font>
'123=
60;124single '123Left aligned
`123=
60;124apostrophy123Right aligned
\u201c123</span>#VALUE!Gives error in formula
^123#VALUE!

Does the web page have single quote or apostrophy or
UTF8 simple or something else?

Thanks

So, with a single quote or apostrophy it is treated as a
number but does change alignment?

Date sent:60;Fri, 8 Apr 2022 07:51:20 +1200

#8

=Column A has number
Column B sees if value can be used in formula (=a+1)
Column C checks isnumber result
Column D checks is text result
Column E checks Format result
Column F checks Type result
Column G =col(a) but format set to $

123124TRUEFALSEF0v=$123.00C2-
'123124FALSETRUEF0l123C2-
`123124TRUEFALSEF0v$123.00C2-
\u201c123&#160;#VALUE!FALSETRUEF0l\u201c123C2-
^123#VALUE!FALSETRUEF0l^123Â-

Interesting that a 'number is not a number but can be used in
formula? But a numeric format is not applied since it is a label
type?

#9

Yes, that got me interested.