How to add character in between values of a cell for every cell?

Question

I am trying to add a character "-" in between the values into every cell for the Date column such as those in row 3 and 4. How can I do that ? I am planning to format it into the date format like row 2. So I would need to add the "-" in order to do so.

Jim K · Answer 1 · 2016-02-17T15:58:42.837

1

To convert the text to dates:

Select the cells that need to be converted.
Go to Data -> Text to Columns.
In the dialog, click on the top of the column labeled "Standard" to select it.
Change the Column Type dropdown to Date (YMD). Press OK.

For step by step screen captures, see http://cca220v.net/blog.php/2012/12/20/convert-cells-from-text-to-date-libreoffice.

edited Feb 17 '16 at 15:58

answered Feb 17 '16 at 15:53

Jim K

12,824
2
22
51

score 0 · Answer 2 · answered Feb 18 '16 at 01:03

This is just an alternate solution. If you want your data to be identified as a date, then the above solution is complete. But I have had occasions when I need it in a specific format which the spreadsheet program doesn't provide by default. I don't use LibCalc, so wouldn't know if you did get a YYYY-MM-DD output format. If not, then this could be the way (though I am not sure it will get recognised as a date format, but mostly as text)

Your data seems to be in YYYYMMDD order. You can insert a column between A and B, and in cell B1, write this formula =CONCATENATE(LEFT(A1,4),"-",RIGHT(LEFT(A1,6),2),"-",RIGHT(A1,2))

How to add character in between values of a cell for every cell?

2 Answers2