Text to columns?

GS-Calc GS-Calc Support
1

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.

2

There is no specific “split” command to do this, however there are still a few methods to split cell contents:

  1. 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.

  2. Saving and loading a given range to/from a text file with different cell separators.

  3. 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).

3

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

  1. Insert a new empty column (Tools - Insert Column) to the right of where you wish to have the data split.
  2. Select fields that have data to be split so only they are highlighted.
  3. Click Edit then Split Cells from the drop-down menu.
  4. 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.