[Solved] Change from text numbers to real numbers in Calc

[jza]

I got a spreadsheet but all the numbers are with an apostrophy so the value is gone as a string as opposed to a integer. Is there a way that I can change this, I have tried search and replace and format and both won't work.

So just to be clear I got this:

Code: Select all

'1.00
'1.50
'1.02

This makes me unable to add the whole ammount.

[Villeroy]

How to "retype" every value:
Select the range(s)
menu:Find>Replace
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+ [dot and plus]
Replace: &
[Replace All]

If a cell has number format "@" it will treat the input as text, any other number format evaluates the input as number,
Number formats alone have never change a single value.

[thomasjk]

Use the Value function. It converts text to values.
A1 B1
'1.00 =value(a1)
'1.50
'1.02

[jza]

Thanks VALUE() work fine.

[sergio]

After a bout a days searching I came up with this answer - perhaps this will help:

When importing csv text into calc, Calc is not able to see the numeric values of a cell as numbers but as text. So assuming that you have been able to separate all the text from the numbers then you could try these 3 steps to make the mass data be numbers and not the ( '123.456.789,00 ) ... particularly when importing bank statements in csv.

In my case the local currency setting used but the banks is : 123.456.789,00 ( you will notice the numeric separator is a " . " and the decimal is a " ," (irritating).

The following steps involve using the find & replace (assuming you know how to use that, and that you can make the criteria apply only to the columns or cells that you want)
Step one:
select the cells or columns with the numbers, (recognised by calc as text in the import) then ask find & replace to find the " . " and replace with... (don't fill it in leave blank). the result should be 123456789,00 (i.e. no " . " just the " , " remains)

Step two:
Ask find & replace to find the " , " and replace with " . " the result should be 123456789.00
This should make calc automatically assume the cell or column values as numbers

Step three:
Formate the numbers cells or columns in you local setting as a number or currency

Your data should be useful now.
Hope this helps,
Regards, Sergio

[Villeroy]

@Sergio

'1.00
'1.50
'1.02

This indicates that jza uses a locale with decimal point.

Values with comma...
1,50
1,02
... would not show the apostroph since they were no numeric expressions at all, and if it's not numeric there is no need to suppress evaluation.
Therefore it would counterproductive to replace any decimal separator.
Conversion by means of VALUE has a draw-back: When the application locale changes, the evaluation may fail. Just switch from a dot-locale to a comma-locale and VALUE("123,456") becomes a different value, VALUE("99.66") returns #VALUE! while VALUE("1.2") may evaluate to a day number (unformatted date).
In-place conversion by means of Find&Replace re-enters all the value without the leading apostrophe, so the evaluation to numbers takes place. Constant numbers keep their unambiguous value regardless of formatting and locale settings.

[mikefromchalfont]

The 'Convert Text To Number (and date)' extension works like a dream - i recommend it - very simple to install and use.

[calc-learner]

@Villeroy - Where do you learn this stuff, man?

Calc geek. Solved my problem.

How to "retype" every value:
Select the range(s)
menu:Find>Replace
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+ [dot and plus]
Replace: &
[Replace All]

If a cell has number format "@" it will treat the input as text, any other number format evaluates the input as number,
Number formats alone have never change a single value.

[Villeroy]

@Villeroy - Where do you learn this stuff, man?

I know 4 ways to write new constant values or edit existing values:

1) Keyboard
2) Clipboard (paste, better: paste-special)
3) Import (open foreign file formats, dump other files' data by means of linking)
4) Find&Replace

Having wrong values in spreadsheet cells, you can
1) re-type them using the keyboard
2) copy the right data into the clipboard and paste over the wrong data using appropriate import settings if applicable.
3) re-open the foreign file or replace wrong data links with new data links using appropriate import settings if applicable.
4) replace wrong contents with correct content. Sometimes you replace all commas with points, points with commas, dashes with slashes, spaces with nothing.
Quite often you want to replace all wrongly imported text content (marked with a leading apostrophe) with itself just as if you would re-type all the cells. Regex (regular expression) .+ matches any amount of any kind of characters. The replacement & matches anything that has been matched by the search expression. Replacing regex .+ with & replaces anything with the same anything. The leading apostrophe in the formula bar does not belong to the anything. It is just a tag to denote the numeric content as a literal sequence of characters. 0123 is another character sequence than 123. If both values were numbers, 123 would be the exact same value as 0123 displayed in different number format. There are number formats to display the exact same number in thousands of different ways.
Most users ask about the right formatting option. But formatting does not apply. Formatting displays the same values in different ways, but we are dealing with plain wrong data. Wrong data formatted to look right would be a very bad idea as soon as you start working with wrong data (calculate, sort, filter etc).

5) You can keep the wrong data and use spreadsheet formulas to calculate the correct values from the wrong ones. If you are lucky, =VALUE(A1) converts any numeric text in A1 into the correct number.