I want to separate Japanese names into family and given names in two separate columns. All have the format Surname_GivenName, where the underscore is a blank space. All surnames and given names consist of a single word, with no other spaces and no punctuation. Is there a straightforward way to do this?
Mac Studio
The TEXTBEFORE and TEXTAFTER function should serve you well in this task.
Here's an example, using English names and following the same naming pattern as you have described.
The formula shown below the table is entered in cell B2, then filled down to the last cell in the column (or to the last row containing a full name in column A)
In column C, C2 contains the same formula, with TEXTBEFORE replaced by TEXTAFTER.
In both formulas, the two " marks are separated by a space character to tell TEXTBEFORE or TEXTAFTER the boundary containing th text they are to stop 'before' or to start 'after' when collecting data to be placed in the cell containing the formula.
Regards,
Barry
The TEXTBEFORE and TEXTAFTER function should serve you well in this task.
Here's an example, using English names and following the same naming pattern as you have described.
The formula shown below the table is entered in cell B2, then filled down to the last cell in the column (or to the last row containing a full name in column A)
In column C, C2 contains the same formula, with TEXTBEFORE replaced by TEXTAFTER.
In both formulas, the two " marks are separated by a space character to tell TEXTBEFORE or TEXTAFTER the boundary containing th text they are to stop 'before' or to start 'after' when collecting data to be placed in the cell containing the formula.
Regards,
Barry
Barry, thanks so much! This was fantastic! Actually, several weeks ago I spent several hours trying to get these two functions to work, because they sounded perfect for my application, but from Apple's abstruse instructions and equally impenetrable examples, I could not get them to work. Your example was a cinch to follow and it worked flawlessly. I am truly grateful!
Separating surnames and given names
