2

My question is similar to this one: SPLIT function in Google Spreadsheets with OR statement

but has the following difference:

I want to use the delimiter "apple" and the delimiter "." within the SPLIT function.

Since the delimiter apple has multiple characters, @parkydr answer here doesn't work.

Example:

On this input on A2:

I ate an apple on the bus home. Going

I want it to split like this:

I ate an / on the bus home / Going

I tried this:

=SPLIT(A2,"apple.",TRUE)

I / t   /   n   / on / th   / bus / hom / Going

and this

=SPLIT(A2,"'apple''.'",TRUE)

I / t   /   n   / on / th   / bus / hom / Going

and this

=SPLIT(A2,"apple.",false)

I ate an apple on the bus home. Going

and this

=SPLIT(A2,"apple"".",false)
#ERROR Formula parse error.

and this

=SPLIT(A2,"'apple''.'",false)
I ate an apple on the bus home. Going

How to make return the next split?

I ate an / on the bus home / Going
1

You'll have to replace all delimiters with a unique delimiter and split.

=SPLIT(REGEXREPLACE(A2,"apple|\.","✱"),"✱")
| improve this answer | |
  • Thanks @TheMaster! It's working. Great way to embed the formulas, I'm beginning to see how to use complex embedded formulas as arguments to other complex formulas. Thanks also for the regex101 link, I bookmarked it and will look to see how to make use of it! Just looked at the quick reference and all tokens and it gives great ideas suggestions of what you can do with it. – Lod Jun 22 '19 at 13:03
  • I didn't know regular expressions could be used within formulas. Now I understand a previous formula I saw with regexes! In the meantime I found this that explains simply regexes uses in details: regular-expressions.info Take care! Talk soon. – Lod Jun 22 '19 at 13:06
  • Just found these too to help with regexes! regexone and Regex tutorial – Lod Jun 22 '19 at 14:10
  • @Lod Great. Keep practising if you're interested. – TheMaster Jun 22 '19 at 14:21
  • I just found this new summary page for regexes quick references: codexpedia .Saved with the archive.org extension – Lod Jun 30 '19 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.