[Calc] - How can I stop all NUMBER FORMATING? (allow raw text input only)

asked 2016-09-25 02:27:04 +0100

INeedHelp
13 ●2 ●3 ●7

updated 2016-09-25 02:28:07 +0100

I can not work out how to stop Clac from automatically formating number strings.....

Basically I am working with Tab- delimitered Text files to be read as a data source by an application I am working with. One of the columns is supposed to be a numbered order list..

1.001
1.002
1.003
...
1.165
2.001
2.002
2.003
etc etc

But no matter what I do it comes out with formatting.. so stuff like this happens...

1.010 -> 1.01
1.600 -> 1.6

I know this is mathematically correct but it is NOT the data I need in the application.

These I have tried these solutions found on this knowledgeable and THEY DO NO WORK
I have gone to Tools/Auto-correct Options and unticked everything in "options" and "localized options" as well as unticking "autoinclude" in "exceptions". This changes nothing.

If I use a "," at the start of the data input ",1.600" then it appears correctly in the column as I want it, but with the "," at the start... thing is when upi export the text file this command is not removed and the data is no good as all the numbers in the column have a "," at the front.

Question
Surly there is a way to just make it STOP doing anything and allow raw text inputs into the columns?

Thanks in advance

edit retag flag offensive close merge delete

Comments

Just a remark: By default a cell decides "autonomously" whether an entered/imported sequence of keystrokes/characters is meant to be a number or text. "Recognition" as an entry of type 'Number' also assigns a numeric format (guessed automatically) then to the cell. "Supposedly no numeric intended" results in assigning type 'Text' to the content, not to the cell. Next edit, next "recognition".

Lupp ( 2016-09-25 18:26:30 +0100 )edit

The fake 'Numbers' format 'Text' ("@") applied to a cell skips the "recognition" simply. Since this also is suppressing the recognition of an entered string as a formula, Cells treated this way cannot calculate and display formula results.
Like so many smart features automatic recognition made a big mess. Do you need proof?

Lupp ( 2016-09-25 18:31:45 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2016-09-25 05:21:10 +0100

mark_t

1005 ●6 ●10 ●16

updated 2016-09-25 22:06:22 +0100

Format the column that will contain the order list as text using the menu "Format", "Cells" and under category select "Text". Then when you type into these cells they will be treated as text. When you save the file as a csv file you can choose tab as the field delimiter and uncheck the option "Quote all text cells".

By setting the cell format you do not need to enter ' at the start of each value.

If the cell already has a number value before you set the text format then you may need to retype the data to the correct format.

If you are importing from text files you can set the imported column to Text instead of General to avoid the treatment as numbers.

Edit to add a further option. If you format a column of cells as number and set the number of decimal places to display as required using "Format", "Cells...", category "Number", then set the required number of decimal places. You can then still use formula to determine the value in these cells. This can then be exported to text by saving as .csv file, during the save dialog set the checkbox for "Save cell content as shown" and clear the other checkbox options. Set the field delimiter as {Tab}.

Attached sample of .ods file with text format in column A and Number format in column B. Untitled 23.ods

See attached csv file as this would be exported following the above instructions. Untitled 23.odt (file type changed to odt so it could be attached.)

edit flag offensive delete link

add a comment

answered 2016-09-25 13:45:35 +0100

m.a.riosv

20163 ●7 ●47 ●123

I think the way it's set up the 'Text import' window to have the numbers imported properly.

edit flag offensive delete link

Comments

I had the impression from the question that @NeedHelp wanted to export the data from Calc to use as input to some other application, but was not sure how @NeedHelp was creating the data in Calc so mentioned the import of text files at the end of my answer.

mark_t ( 2016-09-25 21:48:05 +0100 )edit

Re-reading looks so.

m.a.riosv ( 2016-09-25 22:44:26 +0100 )edit

add a comment

[Calc] - How can I stop all NUMBER FORMATING? (allow raw text input only)

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

[Calc] - How can I stop all NUMBER FORMATING? (allow raw text input only) edit

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

[Calc] - How can I stop all NUMBER FORMATING? (allow raw text input only)