Split text in numbers

So if I understand you correctly, after pasting in from Pages you have something like this?

And you want to split the name into First and Last name, with First in one column and Last in another?

If so, you can do this:

Select the cells with the data you want to split, here A2:A3:

With the cells still selected, choose 'Text to Columns' from the Numbers > Services menu and choose Space as your delimiter (I'm assuming there's a space between the first and last name).

Click once in cell C2 and command-v or Edit > Paste and Match Style, giving you this:

Add header labels for columns C and D and delete column A, resulting in:

SG

P.S. This assumes you are using this Automator Service (Dropbox download).

Hi, I've retested the Service here and don't get an error... UNLESS I have Numbers 2 (part of iWork '09) open at the same time. This Service only works with Numbers 3 and will get confused if Numbers 2 is also open, even if you have a different document open in Numbers 2. Could that be the problem on your machine?

SG

Hi SG,

This came in handy for my list of 24k people. I applied formula "=LEFT(A2,FIND("•",A2,1)−1)" to separate the first and last name from "•" that was in the names. haha, then I went to delete column A and soon found out I needed that info, or is there a way I can hide it or delete it? Thank you

Not sure I understand what you mean, but you can "remove" active formulas and leave just the values by selecting the cells containing formulas, typing command-c to copy, and choosing Edit > Paste Formula Results from your menu. Then when you delete columns you won't get error messages.

SG

What kind of text? What workflow?

SG

you can make sure the text fields are separated by tabs and Numbers will place each field in the next cell of a row

if you want to split based on some other criteria, you can perform a search and replace so that each delimiter is replaced by a tab

More details of your specific situation is better

replace the "|" with a tab, then paste into a table

HenriRaSu wrote:

Actually, haha i don't know. I have a macbook air 2015 OX el capitan, and the version of numbers is 3.6.1(2566).
If there's a problem on my machine, i can't tell... 😟

I didn't mean to suggest there is a problem on your machine! I meant perhaps the reason the Service was having problems was because you had Numbers 2 open along with Numbers 3. Many people run both versions.

There may be something with Brazilian localization the Service does not like. It's hard to debug from here. If you only need to split first name and last name then you can use a much simpler script (there is also a way to do this with formulas that is not hard).

The simpler script is below. Just copy-paste it into Script Editor (in your Applications > Utilities folder).

Then select the cells that contain the first and last names together (A2:A3 in my example, C2 in your example) and click the triangle 'run' button in Script Editor. Then click once in a destination cell (in my example C2) and type command-v or choose Edit > Paste and Match Style to paste the split text into separate columns.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRange to selection range

set pasteStr to ""

tell selRange to repeat with c in cells

set v to c's value as text

set f to v's first word

set l to v's last word

set pasteStr to pasteStr & f & tab & l & return

end repeat

end tell

end tell

end tell

set the clipboard topasteStr

return pasteStr

Hi HRS1,

That's a message you get when Numbers can't find an open document.

For this script to work you must first:

1. Already have Numbers 3 open (and Numbers 2 must not be open).
2. Already have a document open in Numbers 3.
3. Already have selected cells in that document.

Also, make sure Script Editor is checked at System Preferences > Security & Privacy:

SG

The script should be easier to use, but if you always have a first name followed by a blank followed by a last name, then you can do something like this:

In cell B2, copied down the column: =LEFT(A2,FIND(" ",A2,1)−1)

In cell C2, copied down the column: =RIGHT(A2,LEN(A2)−FIND(" ",A2,1))

SG