CODEWITHSUNDEEP
google-sheets
Ricky
Ricky

Reputation: 11

google sheets Split Function

I'm trying to split a cell on the 2nd space character. Is this possible? Or is it just possible to split on a space character?

LeBron James SF ORL @ CLEThu 7:00pm

LeBron James        SF ORL @ CLEThu 7:00pm

Upvotes: 0

Views: 2704

Answers (3)

pnuts
pnuts

Reputation: 59460

A simple way is to SUBSTITUTE the second instance of a space with a character not otherwise in service (I chose £), then SPLIT on that character:

 =split(substitute(A1," ","£",2),"£")

Upvotes: 1

Max Makhrov
Max Makhrov

Reputation: 18707

Please try regular expression:

=REGEXEXTRACT(A1,"^([^ ]+ [^ ]+) (.*)")

  • (...) (...) is to find 2 groups on a string
  • ^ at the beginning means to look at the start of a string
  • [^ ]+ means 1+ no space char.
  • .* means any number of chars

References:

  1. RegexExtract
  2. Regex Syntax

Upvotes: 2

Ed Nelson
Ed Nelson

Reputation: 10259

You can use il of individual pieces of the split. For example if 'LeBron James SF ORL @ CLEThu 7:00pm' is in A1. In B1 put this:

=index(split(A1," "),0,1)&" "&index(split(A1," "),0,2)

It will return 'LeBron James'. In C1 put:

=index(split(A1," "),0,3)&" "&index(split(A1," "),0,4)&" "&index(split(A1," "),0,5)&" "&index(split(A1," "),0,6)&" "&text(index(split(A1," "),0,7),"hh:mm am/pm")

It will return 'SF ORL @ CLEThu 07:00 PM'. Note the use of text to return the time correctly.

Upvotes: 1

Related Questions