Introduction
When using Excel, you often encounter situations where you want to extract specific parts from a text string separated by spaces or symbols. I myself wanted to “separate and obtain the last name and first name from a string where they are separated by a space,” so I investigated how to achieve this using Excel functions.
As a result, I found a way to retrieve the values before and after the space by combining the LEFT, MID, and FIND functions. I will introduce the method below.
Case Scenario: “Last Name + Space + First Name”
In this example, we will assume the cell contains the following content:
- Cell A1: Mori Taro (separated by a space)
We will look at how to extract “Mori” (Last Name) and “Taro” (First Name) individually from this string.
How to Extract the Last Name (Text Before Space)
First, here is the function to extract the last name (the text before the space).
=LEFT(A1, FIND(" ", A1) - 1)
Note: In this example, I am using a standard space. If your data uses a full-width Japanese space (“ ”), make sure to copy that exact space into the quotes inside the FIND function.
Explanation
FIND(" ", A1): Finds the position number of the space character.-1: Subtracts 1 from that position to target the character immediately before the space.LEFT(A1, Number): Extracts the specified number of characters starting from the left side of the string.
Entering this formula into a cell will extract just the characters for “Mori”.
How to Extract the First Name (Text After Space)
Next, here is the function to extract the first name (the text after the space).
=MID(A1, FIND(" ", A1) + 1, LEN(A1))
Explanation
FIND(" ", A1) + 1: Adds 1 to the space’s position to start extracting from the character immediately after the space.LEN(A1): Calculates the total length of the string. (Using the total length is a safe way to ensure you grab everything until the end).MID(String, Start, Length): Extracts characters from the string starting at the specified position.
Using this formula, you can extract just the “Taro” part.
Application: Using Other Symbols like Hyphens
In this example, I used a space, but this same function structure can be applied if the separator is a hyphen (-) or a slash (/).
For example, if A1 contains 123-456:
- Extract 123:
=LEFT(A1, FIND("-", A1) - 1) - Extract 456:
=MID(A1, FIND("-", A1) + 1, LEN(A1))
The mechanism is simple: you only need to change the first argument of the FIND() function to match the delimiter you are using.
Summary
When you want to split and process text strings in Excel, the combination of LEFT, MID, and FIND functions is extremely powerful.
- If the text structure is simple, you can flexibly decompose and process cell contents using only functions.
- This often eliminates the need for complex VBA scripts.
Please give it a try. I hope this serves as a helpful reference.
