Moving last name into a different column and delete from the original

I have a lists that I would like to remove the last name and put it in a new column. This last name should be removed from the original column., If you review the attachment you will see how the ...

excel

HansVogelaar
Feb 28, 2023
JCountryman

If necessary, insert empty columns in columns B and C.

Enter the following formula in B2:

=TRIM(LEFT(SUBSTITUTE(A2, " ", REPT(" ", 255)),LEN(SUBSTITUTE(A2, " ", REPT(" ", 255)))-255))

And in C2:

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 255)),255))

Select B2 and C2, then fill down, for example by double-clicking the fill handle in the lower right corner of C2.

With the range still selected, copy it, then paste as values.

You can now delete column A if desired.

HansVogelaar

MVP

Feb 28, 2023

JCountryman

Attachment?

JCountryman
Copper Contributor
Feb 28, 2023
HansVogelaar

Not sure why it didn't upload. See link below

https://www.dropbox.com/s/o55unbypcz94p6d/Screenshot%202023-02-28%20at%2011.27.23%20AM.png?dl=0
- HansVogelaar
  MVP
  Feb 28, 2023
  JCountryman
  
  Thanks. Will the last name always be a single word, as in your screenshot? Or could there be double-barreled last names such as Andrew Lloyd Webber, or Ralph Vaughan Williams?
  - JCountryman
    Copper Contributor
    Feb 28, 2023
    It will vary but the few there are I can do manually

Forum Discussion

Moving last name into a different column and delete from the original

Resources