I need help with a Google Sheets problem. I want to split the text in a cell, but not at the first space. I’m hoping to divide it at the second space character. Is this doable? Or can we only split at the first space?
Here’s an example of what I’m working with:
Michael Jordan PG CHI vs NYKFri 8:30pm
Michael Jordan PG CHI vs NYKFri 8:30pm
I’d like to separate the player name from the rest of the info. Any ideas on how to make this work? I’ve tried the basic SPLIT function, but it always cuts at the first space. Thanks for any help you can offer!
I’ve actually tackled a similar issue in my fantasy sports spreadsheet. You can definitely split at the second space using a combination of functions. Here’s what worked for me:
=SPLIT(A1, CHAR(32), FALSE, 2)
This formula uses SPLIT with CHAR(32) as the delimiter (which is a space), and the ‘2’ at the end tells it to split only at the second occurrence. It should separate your player name from the rest of the info.
If you need more control, you could also try using REGEXEXTRACT. Something like:
=REGEXEXTRACT(A1, “^(\S+\s\S+)(.+)”)
This will capture the first two ‘words’ (anything not a space) followed by a space, then everything else. It’s a bit more complex, but gives you flexibility if your data format changes.
hey, i’ve dealt with this before. try this formula:
=arrayformula(if(len(A1:A), regexextract(A1:A, “^(\S+ \S+) (.+)”), “”))
it’ll grab the first two words (player name) and separate everything else. works like a charm for me. lemme know if u need more help!
While the previous solutions are valid, I’d like to offer another approach using the LEFT and FIND functions. This method is particularly useful if the player names consistently have two words:
=LEFT(A1, FIND(" “, A1, FIND(” ", A1) + 1) - 1)
This formula finds the position of the second space and extracts everything to the left of it. It’s efficient and doesn’t require advanced regex knowledge.
For the remaining information, you can use:
=RIGHT(A1, LEN(A1) - FIND(" “, A1, FIND(” ", A1) + 1))
These formulas are straightforward and work well for consistent data formats. They’re also easy to adjust if you need to split at a different word in the future.