[Solved] Omit character - read rewrite - extract

[me231]

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; " ' "; ""))

[FJCC]

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.

[me231]

exactly - imported .csv
thanks that is so much more simple - much grass

[FJCC]

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 ".".

[robleyd]

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.