Cell values that keep auto correcting as dates

[cjeanson]

Hey all, run into an issue with cells that libreoffice is trying to deal with as dates.

So, I have cells with values such as:

011925-03-29

Now, when I pasted these values into my spreadsheet, they were automatically converted to:

11925-03-29 and recognized as a date.

So, I want to convert them to text. However, if I set the value of the cell as text, it will convert the “date” to 3661645.

If I properly format another cell, and manually type in the value, it works. However, considering I have thousands of cells to deal with this isn’t an option. If I try special pasting, it doesn’t work either as the cell is seen as a date only.

If I could get the cell copied over as text I could then concatenate a 0 in front of it to fix the issue. However, I don’t know how to copy the cell “value” as is, without it being corrected. I did turn off all auto correction, and even tried clearing out the language setting for date/using m/d but that didn’t help.

What would be the proper way to fix this issue?

[LeroyG]

@cjeanson

when I pasted these values into my spreadsheet

Next time, use Paste Unformatted Text, and, instead of Column Type Standard, choose Text.

[mikekaganski]

@cjeanson

If I properly format another cell, and manually type in the value, it works. However, considering I have thousands of cells to deal with this isn’t an option.

This definitely is the option.

In Calc, which is the spreadsheet software, designed for numeric calculations, the reasonable default is that every user input is considered to be numeric, and so parsed as such. Indeed, what you show fits into the ISO standard date representation, and so is a valid number from Calc’s point of view.

Calc has a dedicated way to stop parsing the user input as numbers: it is when cells are pre-formatted as text. That tells the program that no matter how similar the input to a number is, it must be stored as is, in the textual form. And so, if you have columns that are textual, you simply pre-format the whole column(s) once, which is a few seconds job, and be done. A better way is using dedicated cell style with textual number format, but conceptually it’s the same.

There’s also a way to tell the program that a single input is textual - which is useful when you have a single case, and it’s not reasonable to format the whole range. You simply start your input with a single apostrophe ', and then the rest is considered text: '011925-03-29.

[cjeanson]

Alright, managed to get this to work:

Copied the 10825-05-29 cell value over to an adjacent cell after setting the adjacent cell to text format by using paste special without formatting (menu option, not hotkey) and then it worked for me to concatenate.