Skip to main content

how to separate first name, middle name and last name in excel

Simple way to separate FULL NAME to FIRST name, MIDDLE name and LAST name in excel with find, left and id function presented by 99Excel.Com (Akumar)

First Name :        =IFERROR(LEFT(A2,FIND(” “,A2)-1),A2)

Middle Name : =IFERROR(MID(A2,FIND(” “,A2)+1,(FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)),””)

Last Name :        =IFERROR(IFERROR(MID(A2,FIND(” “,A2,FIND(” “,A2)+1)+1,100),MID(A2,FIND(” “,A2)+1,100)),””)

how to separate first name, middle name and last name in excel



F4 Key use in excel

F4 Key:

“F4” key provides you two different outputs depending on what you are doing currently in your excel.

If you are editing, then it will cycle you through the all three types of references, like – absolute reference, relative reference and mixed reference.

Refer below example for detail.


As displayed in below image in the selected cell of excel, we are first using the relative reference of Cell having Student A’s marks in English Subject. After pressing “F4” key it toggles and suggests the absolute reference of same cell and again by pressing “F4” key the mixed reference of the cell are suggested. If you are new to these terms of references – then you cango through this article to understand these in details.

References - Absolute, Relative and Mixed

In addition to toggling the reference of a cell, “F4” keys also repeats the last action performed in workbook. Although, this could also be achieved using “Ctrl + Y” keys.

Example: If you have just inserted or deleted a row or column in your workbook then by pressing “F4” key you can repeat the action of insertion or deletion of rows and \ or columns. Likewise if you have just pasted something in your workbook, then using “F4” you can repeat it.

Looping Through Code

Using conditional statements and looping statements (also called control structures), you can write Visual Basic code that makes decisions and repeats actions. Another useful control structure, the WITH statement, lets you to run a series of statements without having to re-qualify an object.



Using Conditional Statements to Make Decisions


Conditional statements evaluate whether a condition is TRUE or FALSE, and then specify one or more statements to run, depending on the result. Usually, a condition is an expression that uses a comparison operator to compare one value or variable with another.


Choosing a Conditional Statement to Use


  • ..Then…Else: Branching when a condition is True or False
  • Select Case: Selecting a branch from a set of conditions




Using Loops to Repeat Code


Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition is FALSE; others repeat statements until a condition is TRUE. There are also loops that repeat statements a specific number of times or for each object in a collection.


Choosing a Loop to Use

  • ..Loop: Looping while or until a condition is TRUE
  • ..Next: Using a counter to run statements a specified number of times
  • For Each…Next: Repeating a group of statements for each object in a collection





Running Several Statements on the Same Object


In Visual Basic, usually you must specify an object before you can run one of its methods or change one of its properties. You can use the WITH statement to specify an object once for an entire series of statements.


  • With: Running a series of statements on the same object