The Excel feature, Text-to-Columns, is something I use everyday on my Windows (work) machine. It is extremely frustrating that there is no such command in Numbers that I can click through to separate a column of data that is straight-forward, separated by commas, hyphens, or whatever, and NUMBERS cannot do this.
Extremely frustrating.
MacBook Pro 15", 10.14
To address this problem I wrote this Text to Columns Automator Service (Dropbox download).
It takes about a minute to install. Double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy. Also make sure Automator.app and Numbers.app are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
To use just select the cells with the text you would like to split and choose Text to Columns from the Numbers > Services menu. Answer the prompt about the delimiter. Then click in the upper-left cell of the range where you want the values to go and command-v or Edit > Paste and Match Style to paste them. Unlike in Excel, this does not automatically add columns to the right. You need to make sure you already have enough blank columns before pasting.
SG
To address this problem I wrote this Text to Columns Automator Service (Dropbox download).
It takes about a minute to install. Double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy. Also make sure Automator.app and Numbers.app are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
To use just select the cells with the text you would like to split and choose Text to Columns from the Numbers > Services menu. Answer the prompt about the delimiter. Then click in the upper-left cell of the range where you want the values to go and command-v or Edit > Paste and Match Style to paste them. Unlike in Excel, this does not automatically add columns to the right. You need to make sure you already have enough blank columns before pasting.
SG
Numbers uses the tab as the column delimiter and the carriage return as the row delimiter
When you paste data you can open text edit and replace commas with tabs, then select all, copy, then paste into a table in Numbers. Not perfect, I know
You can always post feedback to Apple using the Numbers menu item "Numbers > Provide Numbers Feedback"
There is an Applescript to do it and it was recently posted. I would post a link to it but I can never find anything on these forums when I try searching, even when it is something recent and I know the exact wording in the post and who posted it. Wait a bit and a link to the script will likely be posted here.
Hi KCox,
To help us help you, please give examples of data that is straight-forward, separated by commas, hyphens, or whatever.
Regards,
Ian.
The data that I am attempting to split is a date column:
2014-03-21
2014-02-19
2015-06-12
2017-08-16
I want to separate the year from the months so that the result will be a single column of ‘years’ and a single column that begins with the month, 03-21.
Now however, I had the brainchild to duplicate the column, search and replace to remove the year from the second column.
I learned another disappointing fact that in Numbers, I cannot Find and Replace on a single column. I did read somewhere to copy the column to a new sheet, Find and Replace on that sheet. Low and behold, Find and Replace works on the entire document, not even restricted to a sheet.
Because this wiped out all the “years” on 50 pages of data, I’m extremely frustrated.
There were too many steps to undo, silly me thought that I could close without saving, but no, Numbers outsmarted me on this one, too, and saved the spreadsheet without me physically saving.
Now I have two columns of month/day, and no years.
Hi KCox,
Open the document.
Menu > File > Revert To > Browse All Versions
Look for a version before you did Search & Replace.
Or, if you have a Time Machine backup, look for a version before you did Search & Replace.
Once you have a version that shows the original dates, try this:
Formula in B2 =YEAR(A2)
Formula in C2 =MONTH(A2)
Formula in D2 =DAY(A2)
Those formulas will work in Numbers 5.3 regardless of whether Column A is formatted as Date & Time, Automatic or Text.
Fill those formulas down.
Save the document, and get back to us.
Regards,
Ian.
Text-to-Columns
