MS Access Query Tip: How to Split Names to Extract First & Last Names

Here’s the scenario. You have data in a table that contains full names, and you want to be able to split the field so that you have first and last names in separate fields. Unlike Excel, Access doesn’t have a simple way to do this.

In order to split the data in a field, you will need to use a combination of the following Access functions:

1. Left
   Syntax: Left (string, length)
   Returns a specified number of characters from the left side of a string.
2. InStr
   Syntax: InStr([start, ]string1, string2[, compare])
   Allows you to search for a character or string within a string, and returns a number telling you the first occurence of one string within another.
3. Right
   Syntax: Right (string, length)
   Returns a specified number of characters from the right side of a string.
4. Len
   Syntax: Len(string)
   Returns a length of a string.

In our sample table, we have a field that shows the names of employees.

Extract the First Name (Access Query Tip)

In your query, create a field and use a combination of the Left and InStr functions to extract the First name.

Left([strName],InStr([strName],” “)-1)

Here’s a screen shot of the function in the Query Design Grid.

Here’s a screen shot of the result.

Extract the Last Name (Access Query Tip)

Create a field in your query design grid and use a combination of the Right, Len and InStr functions to extract the Last name.

Right([strName],Len([strName])-InStr([strName],” “))

Here’s a screen shot of the function in the Query Design Grid.

Here’s a screen shot of the result.

Need more Access tips? Subscribe to our email newsletter so that you never miss another one.

About the author.

Jackie Kiadii has 20 years of Access database development experience, and teaches Basic through Advanced Access courses, including an in-depth 4-day Access immersion course.

Access Database Training in Atlanta, GA and Online

Need more MS Access help? Here are your options.

• Access Training in Atlanta, GA and Online
• Subscribe to our email newsletter.
• Need onsite group training? Our courses are affordable and customized to your needs. Available for groups of 4 or more students.