Hey everyone! I’m working on a spreadsheet and I need some help. I want to split the text in a cell, but not at the first space. I need to split it at the second space. Is this even possible?
Here’s an example of what I’m dealing with:
Michael Jordan SG CHI vs NYKFri 8:30pm
I want to split it so it looks like this:
Michael Jordan | SG | CHI vs NYKFri 8:30pm
Can anyone tell me if there’s a way to do this? Or can we only split at the first space? Thanks in advance for any help!
While the previous solution works, there’s a simpler approach using regular expressions if your spreadsheet software supports them. In Google Sheets, you can use the REGEXEXTRACT function:
=ARRAYFORMULA(REGEXEXTRACT(A1, “(\S+\s\S+)\s(\S+)\s(.+)”) )
This formula splits the text at the second space, creating three columns. It’s more concise and easier to understand than nested functions.
If you’re using Excel, you’d need to use Power Query or VBA for regex. Alternatively, you could use the TEXT TO COLUMNS feature with a space delimiter, then recombine the first two columns.
Remember to adjust the cell reference (A1) to match your data location.
I’ve actually encountered a similar situation before, and there’s definitely a way to achieve what you’re looking for. You can use a combination of LEFT, MID, and FIND functions to split the text at the second space.
Here’s a formula that should work:
=LEFT(A1,FIND(" “,A1,FIND(” “,A1)+1)-1)&” | “&MID(A1,FIND(” “,A1,FIND(” “,A1)+1)+1,FIND(” “,A1,FIND(” “,A1,FIND(” “,A1)+1)+1)-FIND(” “,A1,FIND(” “,A1)+1)-1)&” | “&RIGHT(A1,LEN(A1)-FIND(” “,A1,FIND(” “,A1,FIND(” ",A1)+1)+1))
It looks complicated, but it essentially finds the position of the second space and uses that to split the text. You might need to adjust the cell reference (A1) to match your data location.
Hope this helps solve your problem!