I am trying to split date/time data in a column. It is formatted as MM/DD/YYYY HH:MM. I need to split it by the space so time is in a new column.
In other programs, a “text to columns” feature is available. Is it possible to do so with GS-Calc? I have read through the help section and do not see anything about splitting data.
Thanks.
There is no specific “split” command to do this, however there are still a few methods to split cell contents:
-
Using the find(), left(), mid() and right() functions with array/range arguments. After the output data is generated the formula can be converted to a value or one can use the “Formula Composer” dialog box to insert the formula result as values/strings at once.
Find() optionally uses regular expressions so the splitting pattern can be much more compound.
-
Saving and loading a given range to/from a text file with different cell separators.
-
Using a script.
I think the upcoming ver. 14 should include some additional improvements re: this functionality (e.g. a specific “splitting” function or command or some ready to use scripts).
I’m pleased to report in version 14 using Edit-Split Cells and pressing the space bar worked perfectly for what I wanted to do (for anyone reading this later, obviously any character could be used in the box to split the cells, I just needed the space to do this). Thanks for the update, this program has been a lifesaver for my huge files.
Process (version 14):
- Insert a new empty column (Tools - Insert Column) to the right of where you wish to have the data split.
- Select fields that have data to be split so only they are highlighted.
- Click Edit then Split Cells from the drop-down menu.
- In the Split Cells - Separator box that appears, type in the character that appears where you wish to use to split them. In my case, I had a column of DD/MM/YYYY HH:MM. I used the space bar. Click OK.
Your split data will appear in the new column from #1 above. I had the original column of DD/MM/YYYY and a new column of HH:MM.