I need help with Google Sheets text manipulation. I have a cell containing player information and I want to separate it at the second space character instead of the first one. Is there a way to do this with built-in functions?
For example, I have data like this:
Stephen Curry PG GSW vs LAL Fri 8:30pm
I want to split it so I get:
Stephen Curry PG GSW vs LAL Fri 8:30pm
Basically, I want to keep the player’s full name together and separate everything else. Can the SPLIT function handle this, or do I need a different approach? I’ve tried basic splitting, but it only works on the first space, which breaks up the name.
SPLIT won’t work here since it breaks on every delimiter. You need FIND and MID functions to grab the second space and pull text from there. Use =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) for the first part (Stephen Curry) and =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,LEN(A1)) for the second part (PG GSW vs LAL Fri 8:30pm). The nested FIND finds the first space, then hunts for the second space from that spot. I’ve done this with similar data parsing - works great if your format stays consistent.
try ARRAYFORMULA with a SUBSTITUTE trick - replace the 2nd space with a pipe, then use SPLIT. Something like =SPLIT(SUBSTITUTE(A1," ","|",2),"|") and you’ll get both parts in separate cells automatically. Way cleaner than nested FIND functions and handles formatting better.
REGEXEXTRACT works better for this pattern matching. Try =REGEXEXTRACT(A1,"^(\S+ \S+) (.*)$") - the first capture group grabs the two-word name, the second gets everything after. But this only works if all names are exactly two words. For something more flexible, use SUBSTITUTE to replace the second space with a unique character like | then split on that. Use =SUBSTITUTE(A1," ","|",2) first, then split on the pipe. I’ve found this way more reliable with sports data since some players have middle names or nicknames that mess up the pattern.