Q: I'd like to separate a full name into multiple cells.
-
Jun 1, 2016 10:39 AMby Barry,HI Jon,
Here's one way:
- Select all of the cells containing names*
- Copy
- Open a new Numbers document**
- Click the T button to insert a Text box.
- Click in the text box and Paste
- Press command-F to open the Find dialogue.
- Click the v beside the gear and choose Find and Replace from the menu.
- Click in the Find box. Type a single space.
- Click in the Replace box. Press option-tab to place a single tab character in the box.
- Click Replace All.
This replaces all the spaces with tabs. - Click in the text box, press command-A to select All, Copy.
- Click in the top left non-header cell in the empty table of this new document. Paste.
- Check the results and adjust as necessary.***
My results with your sample data shown here:
Notes:
* Issues like the placement of John's name may make it easier to handle each column of names separately. First column pasted after selecting (single click) A2 (on this table with no header column) and pasting, Second column pasted after selecting E2.
** Find will find all of the spaces in the document. Replace All will replace all of those spaces. Placing the copied list on a separate document preserves the original data until you want to change it.
*** Generally, the adjustment will consist of locating entries (like the first row above) where one name (John's) is in the wrong columns. The adjustment consists of selecting the first item out of place, and all items of that name lying to the right, then clicking and dragging the first item to the right and dropping it in the same row of the correct column.
More than one line can be adjusted at the same time, but only if they can be selected in a single group, and all start in the same column. Sorting on columns with empty cells that should contain data can be used to gather rows needing the same adjustment together for a bulk move to the correct locations.
Example:
Sorting the new table on column C will bring all lines with no entry in this column into a group either at the top or the bottom of the list. These will be the single names (no entry in the 'second owner' column) with only a first name and surname (no middle name or middle initial). Select all the surnames in column B, then click on the top one and drag it (and the rest) one column to the right, dropping it on the same row of column C.
Regards,
Barry
-
Jun 2, 2016 3:42 PMby SGIII,A script can be efficient at this sort of task (no scripting knowledge needed to use; just copy-paste).
To use:
- Copy-paste the script below into Script Editor (in Applications > Utilities).
- Select the cells with the names to split (in this example A2:A6).
- Command-c to copy the contents of the cells to the clipboard.
- Click the triangle 'run' button in Script Editor.
- Click once in an upper-left destination cell (in this example C2) and command-v to paste.
You're done in less than a minute.
SG
set theList to paragraphs of (the clipboard as «class utf8»)
set pasteStr to ""
repeat with i in theList's items
set {firstName, middleInit, lastName, theSuffix} to {"", "", "", ""}
tell i
set firstName to word 1
if (word 2's length > 1) and (i's words)'s length ≥ 2 then
set lastName to word 2
try
set theSuffix to word 3
end try
else
set middleInit to word 2
set lastName to word 3
try
set theSuffix to word 4
end try
end if
end tell
set pasteStr to pasteStr & ¬
firstName & tab & middleInit & tab & lastName & tab & theSuffix & return
end repeat
set the clipboard to pasteStr
return pasteStr
-
Sep 26, 2016 11:07 AMby Barry,Hi AG,
I'll leave the script editing to SG, but he may find some additional information useful:
- Do all the names in the list have the same format: last, first
- Are there any hyphenated names: eg. Smith-Jones, Jane
- Any titles/honorifics: Anderson, Dr. Allison
- Any suffixes/degrees: Cooper, John, LLB QC
- Any other diversions from the basic pattern?
- Which order do you want the results: Last First or First Last
Regards,
Barry
