Extract first last name in Excel
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.
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.
You can find similar Excel Questions and Answer hereunder
1) How can I set up a dynamic named range that expands automatically when new items are added to the list?
2) How do I have proper (Capitalize the first letter in each word of a text) text in cells ?
3) How do you know which row was used the last. Here the explanation to find it with VBA
4) How can I extract file name from a full path?
5) How do you know which column was used last, Here the explanation to find it with VBA
6) How can I get the last non-zero value in a row?
7) How can I extract file name from a full path including folder path and file name?
8) I track a stock on a daily basis and enter the Open, High, Low and Close values for every trading day. In Excel, how can I automatically get High and Low values for the last 10 trading days?
9) How can I find the last used cell in a Column in VBA?
10) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?