The Problem
You have a column of full names (e.g., John Smith) and you need them in two columns: John and Smith. Doing this manually for hundreds of rows is painful.
The Easy Way: Use SheetXAI
If you don't want to mess with formulas, the fastest way to do this is simply by asking.
With SheetXAI, you can open the sidebar and type:
Split the full names in column A into First Name and Last Name columns.
SheetXAI will instantly write the formula or script for you and fill the cells. It handles the syntax so you can focus on the result.
The Manual Way: The Formulas You Need
To do this manually, you need to use: LEFT, RIGHT, SEARCH, and LEN.
1. LEFT
Extracts characters from the beginning of a string.
Syntax: =LEFT(text, [num_chars])
- text: The string containing the characters you want to extract.
- [num_chars]: The number of characters you want to extract.
Example:
=LEFT("John Smith", 4) returns "John".
2. SEARCH
Finds the space character.
Syntax: =SEARCH(find_text, within_text)
- find_text: The text you want to find (a space " ").
- within_text: The text in which you want to search.
Example:
=SEARCH(" ", "John Smith") returns 5.
3. RIGHT
Extracts characters from the end.
Syntax: =RIGHT(text, [num_chars])
- text: The string containing the characters you want to extract.
- [num_chars]: The number of characters you want to extract.
Example:
=RIGHT("John Smith", 5) returns "Smith".
4. LEN
Gets total length.
Syntax: =LEN(text)
- text: The text whose length you want to find.
Example:
=LEN("John Smith") is 10.
Understanding the Logic
For First Name: Take characters from the left up to (but not including) the space.
For Last Name: Take characters from the right. How many? Total length minus the position of the space.
The Final Formula:
First Name:
=LEFT(A2, SEARCH(" ", A2) - 1)
Last Name:
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
Conclusion
Now you know the "classic" way to solve this using formulas. It's a great skill to have.
But for those times when you just want the job done without the mental math, SheetXAI is there to help.
