Re: [libreoffice-users] Clearing formatting.

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

On 07/04/2022 13:18, Michael D. Setzer II wrote:
> 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..
>
>
>
> On 6 Apr 2022 at 10:58, Michael D. Setzer II wrote:
>
> From:                   "Michael D. Setzer II"
> <msetzerii@gmail.com>
> To:                     Steve Edmonds
> <steve.edmonds@ptglobal.com>,
>                 users@global.libreoffice.org
> Date sent:              Wed, 06 Apr 2022 10:58:56 +1000
> Subject:                Re: [libreoffice-users] Clearing formatting.
> Send reply to:          msetzerii@gmail.com
> Priority:               normal
>
> > =On 6 Apr 2022 at 10:13, Steve Edmonds wrote:
> >
> >
> > Date sent:60;Wed, 6 Apr 2022 10:13:51 +1200
> > To: 0;users@global.libreoffice.org
> > From: &#160;Steve Edmonds
> > <steve.edmonds@ptglobal.com>
> > Subject: ;[libreoffice-users] Clearing formatting.
> >
> >
> > > 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.</span>
> >
> > Not sure this would work.
> > Assuming you are using text formatting dialog option.
> > You could click on the other option in addition to
> > whatever filtering options you are using. Then put the
> > apostrophe in the position after the other.
> > You will end up with a column with the blanks where the
> > apostrophy was. Would then just delete that column.
> > That does assume that all numbers in column have
> > apostrophy and that other cells don't have apostrophy's?
> > Another option that might work for you.
> > Past data as currently done.
> > In another column to right put formula
> > No leading &quot; an assuming D1 is cell with '2982 format
> > numbers. Tried to match with "`" but it didn't work?? Had
> > to use code and ascii value for `'
> > \u201c=IF(CODE(LEFT(D1,1))=39,VALUE(MID(D1,2,20)),d1)
> > If all numbers have ` you could just use.
> > No leading &quot;
> > \u201c=VALUE(MID(D1,2,20))
> >
> >
> > Then copy the formula going down.
> > Then highlight. Ctrl-C, and the Shift-Ctrl-V over original
> > data, rather than doing one cell at a time.
> >
> >
> > Perhaps one of those would work with your data.
> > Good Luck. Have a nice day.
> >
> >
> >
> >
> > > 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
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/> Privacy Policy: 
> > > https://www.documentfoundation.org/privacy
> >
> >
> >
> > +------------------------------------------------------------+
> > Michael D. Setzer II - Computer Science Instructor
> > (Retired)
> > mailto:mikes@guam.net                      &#160;
> > mailto:msetzerii@gmail.com
> > Guam - Where America's Day Begins                    &#160;
> > G4L Disk Imaging Project maintainer
> > http://sourceforge.net/projects/g4l/
> > +------------------------------------------------------------+
> >
> >
> >
> >
> > --
> > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
> +------------------------------------------------------------+
>   Michael D. Setzer II - Computer Science Instructor
> (Retired)
>   mailto:mikes@guam.net
>   mailto:msetzerii@gmail.com
>   Guam - Where America's Day Begins
>   G4L Disk Imaging Project maintainer
>   http://sourceforge.net/projects/g4l/
> +------------------------------------------------------------+


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy