LEFT, MID, RIGHT, FIND for surgical extraction
SPLIT and Text to Columns work when there is a clear delimiter. They fail the moment the data is irregular — a name without a middle initial, an address without a comma, a phone with no separator at all.
For irregular data, you need surgical extraction with LEFT, MID, RIGHT, and FIND.
The four surgical functions
| Function | What it returns | Typical use |
|---|---|---|
LEFT(text, n) | The first n characters of text. | First name when split on the first space. |
RIGHT(text, n) | The last n characters of text. | ZIP code (5 chars from the right). |
MID(text, start, n) | n characters starting at position start. | Middle initial. |
FIND(needle, text) | The position of needle inside text. | Locate the comma you want to split on. |
Combining FIND with LEFT to pull the street
If a cell contains 1247 MLK Jr Dr SW, Atlanta, GA 30310, you can pull just the street by combining FIND and LEFT:
=LEFT(D2, FIND(",", D2) - 1)
Read inside-out: FIND returns the position of the first comma (in this case, 19). LEFT then returns the first 18 characters: 1247 MLK Jr Dr SW. The - 1 is there because you do not want to include the comma itself.
Splitting a full name with a middle name
“Maria Elena Vasquez” is the canonical hard case. SPLIT on the space gives three cells, and you do not know in advance which one is the last name. The reliable move is to find the last space and split there.
The Google Sheets formula for that uses REGEXEXTRACT:
=REGEXEXTRACT(A2, "^(.*) (\S+)$")
This returns two capture groups: everything before the last space, and everything after. You can wrap it in INDEX(SPLIT(REGEXEXTRACT(...), "|"), 1) patterns, but the cleanest version is the two-formula version: one cell for First+Middle, one cell for Last.
Action: Mark this page complete when you have finished the activity above.