How to convert text to number format in cells
It is possible to convert text format to number format (change format of the cell) In Open Office Calc (Excel). If the text cell contains a number and text to number format conversion is made, the cell will contain a single quote character directly in front of the number.
This single quote character is not replaceable by Search and Replace function in standard way. The correct Solution to remove the single quote character via search and replace is to use regular expressions - advanced tab.
1. Use search and replace.
2. Click More options and check regular expressions.
3. Set the search string to '.*' (without the quotes) and replace string to '&' (again, without the quotes).
after converting all cells (containing numbers) from text to number format (format cell), very strange single quote character appears and is not deletable normal way - e.g. by search and replace.
Problem is that this character is only deletable by manually editing every cell - and this is in case of hundreds of cells really unfortunate solution.
The associated problem is that until the character is removed, the cells are not displayed properly as numbers - see example image, which I attach.
How is it possible to batch remove this weird character?
How should Calc know that the values in the cell you set from text to number is really needed as number. Sometimes it's not intended to change numbers formatted as text to numbers.
If you format such cells as number, the values are displayed with an leading apostrophe marking them as text. To convert them to numbers, do a search and replace on the selected cell range and set the search string to '.*' (without the quotes), with replace string to '&'. Under more set regular expression and selection only.
Good stuff, very nicely done.
There was merrily!
There was merrily!
I wish you health!
using OpenOffice 3.0.
need to use the regex ".*" w/out the leading quote. cause me quite a bit of frustration, but after doing so the find/replace did the text -> number conversion in a flash.
Very useful. Thank you
thank's for sharing...
It helped me a lot ! 10x
I had the same problem and found find and replace worked for positive numbers but not for negative values.
Great! Really help me. Thanks.
please give me step by step procedure to automatically convert encoded figure in a cell to text in other cell..ty