How do I split the contents of a cell in Numbers?

Question

Level 1

12 points

How do I split the contents of a cell in Numbers?

I have a column of cells in a Numbers spreadsheet that contains city and state information. If that information had been entered consistently, I could use the RIGHT function to pull out the state. However, some cells have, for example, Greenville, SC, while others have Greenville, South Carolina.

In Excel, I can split the contents using a Text to Columns function built into Excel. Is there a way to do that in Numbers (most recent version)? If there is nothing built into Numbers, could I use a regular expression (GREP) in Numbers to do that? How?

MacBook Pro (15-inch, 2017), iOS 11.3, TB, Core i7, 3.1GHz, 16GB, 1TB

Posted on May 12, 2018 4:58 AM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,981 points

May 12, 2018 1:00 PM in response to allenkenya

Hi Allen,

Assuming that all of the cells in Column A have City, State, this may work for you.

Formula in B2 (and Fill Down)

=FIND(",",A2)

Formula in C2 (and Fill Down)

=LEFT(A2,B2−1)

The -1 is to allow for the space after the city name.

Formula in D2 (and Fill Down)

=RIGHT(A2,LEN(A2)−B2−1)

Please call back with questions.

Regards,

Ian.

Reply

Answer 2

SGIII

Level 8

37,604 points

May 12, 2018 7:42 PM in response to allenkenya

allenkenya wrote:

In Excel, I can split the contents using a Text to Columns function built into Excel. Is there a way to do that in Numbers (most recent version)?

In Numbers you can do something very similar using this Text to Columns Automator Service (Dropbox download).

To install (this takes less than a minute) double-click the downloaded .workflow package and give permission at System Preferences > Security & Privacy. Also make sure Automator.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

To use:

select the cells you want to split
choose Text to Columns from the Numbers > Services menu.
answer the prompts
click once in the top-left cell of the range where you want the results to appear
command-v or Edit > Paste and Match Style to paste.

You'll know within a minute or two whether this does what you want.

SG

Reply

Answer 3

allenkenya Author

Level 1

12 points

May 12, 2018 7:42 PM in response to SGIII

I had to install it twice to get it to show up, but it works. Thanks. When it separates "Danville, Virginia" using the "," as the separator, it leaves a space before "Virginia", but I think I can go into Automator and fix that.

Reply

Answer 4

allenkenya Author

Level 1

12 points

May 12, 2018 1:01 PM in response to Yellowbox

Thanks. That's what I was looking for. Sure wish Numbers had a Split Cell Contents function that could split contents based on a space or character such as a comma.

Reply

Answer 5

Barry

Level 9

60,041 points

May 12, 2018 3:15 PM in response to allenkenya

Hi Allen,

'Wishing' won't make that happen. Using the Provide Numbers Feedback in the Numbers menu to post a Feature Enhancement Request directly to Apple might, though.

Regards,

Barry

Reply

Answer 6

allenkenya Author

Level 1

12 points

May 12, 2018 4:18 PM in response to Barry

Yep. Already done that.

Reply

Answer 7

SGIII

Level 8

37,604 points

May 13, 2018 8:10 AM in response to allenkenya

Thanks Allen. Helpful. Keep posting here!

SG

Reply

Answer 8

allenkenya Author

Level 1

12 points

May 13, 2018 5:25 AM in response to allenkenya

I modified the service to incorporate my specific case where there is a comma plus a space between the city and state. The service now deletes both. The update can be downloaded at this link, Text to Columns Automator Service (Dropbox download). When decompressed, the filename will be the same as the original workflow file, Text to Columns.

Reply

How do I split the contents of a cell in Numbers?

Similar questions