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.

Solution:
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).

Original question:
Hello,

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?

Reply:
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.

:: 04.10.2006 :: section English :: submitted by Daniel :: [*] ::

Discussion:

wrote Sunil Kumar on 11.04.2007 at 11:42:20

Good stuff, very nicely done.
http://danuegonax.com
There was merrily!

wrote NopAponurry [web] on 29.11.2007 at 21:50:04

There was merrily!
http://unikont.com
I wish you health!

wrote NopAponurry [web] on 19.12.2007 at 12:42:14

very usefull

wrote RealEstateBroker [web] on 23.04.2008 at 13:01:29

sor

wrote RealEstateBroker [web] on 23.04.2008 at 13:02:35

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.

wrote jimbox [web] on 24.06.2009 at 05:37:42

Very useful. Thank you

wrote ravi on 29.07.2009 at 14:20:43

vary nice.

thank's for sharing...

wrote makki on 03.12.2009 at 08:29:47

200,,10

wrote nannette on 09.12.2009 at 22:20:41

It helped me a lot ! 10x

wrote Chris on 08.07.2010 at 15:10:51

wrote on 14.10.2010 at 16:20:02

I had the same problem and found find and replace worked for positive numbers but not for negative values.

wrote Chris on 18.01.2011 at 22:36:00

Great! Really help me. Thanks.

wrote Alberto on 07.04.2011 at 21:41:59

please give me step by step procedure to automatically convert encoded figure in a cell to text in other cell..ty

wrote nail on 14.04.2011 at 15:07:29