How do a break apart a persons name listed in one column, i.e. - John Smith, into two columns, i.e- column A-John, column B-Smith?

Robin Hirschfield 20 Reputation points
2024-09-27T13:32:27.9533333+00:00

I need to sort data in an excel spreadsheet by last name but currently all the data in the column is listed as first name, last name. Is there a way I can break this data apart into two new columns, first name and last name?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,875 questions
0 comments
Accepted answer
  1. Viorel 117K Reputation points
    2024-09-27T18:38:57.2133333+00:00

    Try to enter this formula to A1: =TEXTSPLIT(E1," ",,TRUE), where E1 contains “John Smith” (for example). The results will appear in A1 and B1.

    If required, use ; instead of ,.

    You can also put =TEXTBEFORE(E1," ") in A1 and =TEXTAFTER(E1," ") in B1.

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.