Excel – separating first and last name from a single column

So this might only be exciting to me, but since i need to deal with data that the dear hubby enters into spreadsheets in not format that is useful to me and since i’ll probably forget rather quickly how to do this…

I found a neat trick to separating a single column into first name and last name – or basically first word in column from the rest!

I found it on a blog called Excel Hints what a concept eh?  Better subscribe to that blog right quick!

Basic formula:


Cell A1 contains the full name “John Smith”

First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.

Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Smith”.


So simple, elegant and totally useful!

Now, i need something as easy to separate a full address…  I’m a noob at macro’s – some day i’ll go to a training class or sit down and learn it… now where is my bucket list – let me add this to it!

2 thoughts on “Excel – separating first and last name from a single column

  1. Hi Nicole – glad you found the formula useful. Thanks for linking back to it. Shoot me an example of an address you need to separate and I can see if I can help.

    • Wow! How cool!
      Normal address stuff:
      55 Main Street
      Anywhere, CO 23456

      I would need to strip the street address out, city, state and zip.

      I wont even fathom to think what i’ll need to do with international addresses!

      Thanks thanks!

Comments are closed.