No announcement yet.

Split 1st Name & Surname

  • Filter
  • Time
  • Show
Clear All
new posts

  • Split 1st Name & Surname

    Hi everyone,

    I don't know Excel very well and I've got a huge list of people's names, but their first and last names both occupy a single cell. I'd like to arrange the list to have one cell for their first names and another for their surnames - is there a quick way to get Excel to do that? The list is incredibly long and I'd love to get around doing this manually... is there a formula-based solution? I am using Office 2000.

    Thanks in advance!

    PS: I don't know what other detail to provide, if this is too vague, please let me know what else I can include.

  • #2
    Re: Single-cell Name & Surname Into 2 Cells

    Use Data>Text to Columns... Or

    =TRIM(LEFT(A1,FIND(" ",A1))) In B1
    =TRIM(MID(A1,FIND(" ",A1),256)) in C1
    Copy down via Fill Handle


    • #3
      Re: Single-cell Name & Surname Into 2 Cells

      The formulas will give two new columns. Assuming the names start in cell A1, put this formula in cell B1 and fill down:

      =LEFT(A1,FIND(" ",A1)-1)

      and this in cell C1 and fill down:

      =RIGHT(A1,LEN(A1)-FIND(" ",A1))

      There are other ways to do this.


      • #4

        Re: Split 1st Name & Surname

        Wow, you guys are amazing, that worked so well!! You have no idea how much time you have just saved me! THANK YOU!!!