ok folks - thanks in advance
i have a spreadsheet source that has an unwanted character leading tmhe important values..
in each cell there is a ' in front of numerical value
example '-12.45
such that when i sumif(a1:a12;">0"). or <0. the return is null because of this ' character =
i need to read the column and rewrite only the value - omit the leading character....
please help me - today - - thanks again
=substitute(a1;"'";"") does not work
i think i got it - but kchime in -
usiung Value seem to work
=VALUE(SUBSTITUTE(A1; " ' "; ""))
[Solved] Omit character - read rewrite - extract
[Solved] Omit character - read rewrite - extract
Last edited by me231 on Tue Apr 02, 2024 6:33 pm, edited 2 times in total.
openOffice 4.0.0 on macOS
Re: omit character - read rewrite - extract
The apostrophe is not part of the cell content. It is displayed in the formula bar to indicate that the cell content is text, despite looking like a number. How were these cell value entered? If they were imported from a text (csv) file, you can adjust the import process to avoid the whole problem.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: omit character - read rewrite - extract
exactly - imported .csv
thanks that is so much more simple - much grass
thanks that is so much more simple - much grass
openOffice 4.0.0 on macOS
Re: [Solved] Omit character - read rewrite - extract
When you import the csv, you should see at some point a dialog labeled Import Text. At the bottom of that there is a display of a few rows of data. At the top of each column, there is a label that says Standard. You can right click on that and set the data type of the column. For your numbers, you probably need to select US English. I'm guessing that in your locale the decimal separator is not ".".
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Omit character - read rewrite - extract
Also make sure to select Detect special numbers.
In addition [Tutorial] Text to Columns has instructions on elimination of the initial apostrophe which prevents values from being recognized as numbers/dates/times.
In addition [Tutorial] Text to Columns has instructions on elimination of the initial apostrophe which prevents values from being recognized as numbers/dates/times.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers