Forum Discussion

JCountryman's avatar
JCountryman
Copper Contributor
Feb 28, 2023
Solved

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 list is setup. 

 

I am a beginner

excel
  • HansVogelaar's avatar
    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.

Resources