How to split text into different cells?

I assume you are posting in this thread because you need to separate imported text into columns. If so, follow the instructions in the post above (the one marked Solved). In addition to the steps described there, you may need to check Automator.app at System Preferences > Security & Privacy > Privacy > Accessibility. The whole process should take less than a minute.

SG

I just did a test export from Outlook 2016 (Windows), choosing as CSV, and got the CSV format I expected, not the format in your post.

I selected the file in Finder, hit the space key, and in Quick Look it looks like this.

SG

Hi, yes sorry I think I may have not replied to the correct post. I downloaded the applescript thing and installed it. I followed the instructions however my data is separated by a line break and not a space, comma, semi-colon, tab etc. In Excel I had to manually enter the ascii code for line break (I think it was 0100 or something.

Is there a way to do that with this plugin because I did not see that option. If not then is there another solution?

To my knowledge csv data cannot be delimited within a line by a line break. The line breaks are to separate each line of data from the next. Same as in Excel. (Just double-checked).

If you have a special data format that isn't csv (character-separated-values) then you should start a new thread and give more details on exactly what format of data you are trying to bring into Numbers.

SG

The data comes from MS Outlook exported to a CSV file. For some weird reason (hey, it is Microsoft), when opened in Excel or Numbers the address data is not in separate columns but on separate lines in the same column separated by a line break.

Example:

Col1 Col2

Row1 1 Garden Rd Email@email.com

Oxford

Surrey

RH33 4KL

Row2 2 Garden Ln

In Excel I split this with the text to columns function but this has the option to enter your own delineator. I found the ascii code for line break and entered it into the box and it worked.

Numbers doesn't have this option so I am looking for a solution. I may have to just open it on a window computer I think.

That's awesome, thank you! Is there a way of making it separate on a line break within a cell? I address data on separate lines within one cell which I want to split into columns. In Excel I can enter the ascii code instead of choosing comma, space, tab etc.

Dear SG,

Would you have an AppleScript similar to this one to transpose a selected range of cells in Numbers?

I know the "Transpose Rows and Columns" option under the Table menu option but, as you would know, it transposes the whole table or else you have to take the row or column outside to another table and bring it back after being transposed. Thank you!

Alper

Hi Alper,

As this script would solve a different issue, I would suggest you repost your question as a New Question, directed to the community. Doing so would help future seekers of answers to a 'transpose' question, who would be unlikely to look to a topic labeled as a 'split text' question.

Regards,

Barry

I just did that. Thx.

AppleScript for transposing only a range

Alper

Hi,

Numbers doesn't have that Text to Column feature built-in. You can write expressions to split strings yourself, but it can be tedious depending on how your original text is formatted.

Here's a simple example:

Sorry I don't have a better solution for you. It really gets sticky when you have more than two fields to deal with and if the separators are not all the same. In this simple example, I use the substring extractors LEFT and RIGHT to parse out the first and last names based on where the separator ", " is found.

Jerry

This Text to Columns Automator Workflow (Dropbox download) does much of what the 'Text to Columns' wizard does in Excel.

To install (one-time): double-click download .workflow package and, if necessary, click 'Open Anyway' in System Preferences > Security and Privacy.

To use:

1. Select the cells in column you want to split (just as you would in Excel).
2. Choose 'Text to Columns 'from your Numbers > Services menu
   
3. Click once in the top-left destination cell (I usually pick a cell in the column to the right of the original values so I can double-check results), and command-v to paste.

Thanks to a sophisticated AppleScript handler (subroutine) written by Nigel Garvey at MacScripter.net, this can handle many of the "real world" situations you are likely to encounter with different formats, separators, etc.

SG

actually the option doesnt exist as part of the package, but Numbers is scriptable, meaning we can add to its functionality using applescript. Thanks to SGIII for posting this code.

I plan on looking it over to see how it works too, teach me something more about applescript hopefully.

Jason

@Jerry, Good to see you back posting here! You may have noticed that your Gantt template has attracted more views than (I suspect) any thread ever. People are still clamoring for a copy.

@Jason, when you figure out how Nigel Garvey's handler does its magic, I hope you will share the explanation. I can explain how I did the rest of the script, but

that handler is truly inspired. Hiroto posted a similar script here in the Numbers discussions, which is very good too.

@FakhraKH, thanks for the green tick.

SG

Hello FakhraKH,

Thank you for the script file, however, i keep getting an error message "Did you select ONE column of cells with contents to split?" and there is only the option to cancel.

Please help, I really don't want to make the shift to excel.