extract first last name in Excel

Q14. In Excel, how can I extract First Name and Last Name from a cell that has Full name?

For example, I want to separate the First Name and Last Name from the cell value "Roger Federer" and have them in separate cells.

To do it in Excel, here is the answer:

a) Enter the formula =LEFT(S3,FIND(" ",S3)-1) in the cell where the "First Name" is required.

The formula "FIND(" ",S3)" returns the position of the space character. Subtracting 1 returns the position of last character of First Name.

The LEFT function is then used to extract all the characters upto last character of First Name.

excel extract first last name

b) Enter the formula =RIGHT(S3,Len(S3)-FIND(" ",S3)) in the cell where the "Last Name" is required.

The formula "FIND(" ",S3)" returns the position of the space character. Last Name is to the right of this space character. Subtracting the position of space character from length of original string gives the length of Last Name.

The RIGHT function is then used to extract all the characters after space character.

excel excel extract first last name

 

You can find similar Excel Questions and Answer hereunder

1) How can I get users to select a folder to save the output of my macro?

2) After several iterations, I have finalized my WorkBook - how do I make it Read-only from then on to prevent further edits?

3) How can I add and link a Chart Title to a cell value?

4) How can I remove Outline from my WorkSheet?

5) How can I loop through all WorkSheets and get their names using VBA?

6) How can I set up a dynamic named range that expands automatically when new items are added to the list?

7) How can I delete all shapes in a WorkSheet?

8) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?

9) How can I prevent other users from adding or deleting Sheets in file?

10) How can I get the last non-zero value in a row?

11) How to add a link in a sheet to another sheet

12) How do I have proper (Capitalize the first letter in each word of a text) text in cells ?

13) How can I ensure that user enters only certain acceptable values in an input cell?

14) How can I convert Column numbers into Column names for use in range definition?

15) How can I fill a series of data automatically?