In sheets, say I have this:

Word Onset

My regexextract in Onset looks like this:


Aka: Extract the non-vowels that are preceded by a . (and an optional ˈ or ˌ) and followed by vowels.

I want to extract all instances within a word--this catches only the t in ˈbɑː.tiɾ and throws a #N/A with the others, and if I add another one:


This will only catch the ɾ and ɕ within bɐ.ˈɾi.ɕiɾ but throw a #N/A for the rest.

I've tried clustering and adding a plus like so: =REGEXEXTRACT(A99,"([.ʔ]ˈ?ˌ?([^ɐɛiouɪɑʊɔ]+)[ɐɛiouɪɑʊɔ])+")

The the result I get is this:

Word Onset
bɐ.ˈɾi.ɕiɾ .ɕi ɕ

Which, uh, not what I wanted.

(and I tried the {A, B} thing, and adding the second set with a question mark...)

So what I'm looking for is

Word Onset
bɐ.ˈɾi.ɕiɾ ɾ ɕ
ˈbɑː.tiɾ t
ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe b tr ɕ

Or maybe

Word Onset
bɐ.ˈɾi.ɕiɾ ɾ, ɕ
ˈbɑː.tiɾ t
ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe b, tɾ, ɕ

I've tried (Oh, my god, have I tried) to look for a solution, but ultimately I don't understand regex well enough, to begin with, to properly apply the suggested solutions I've found... I'm hoping someone will bend this out of iron rail for me.

  • As noted by TheMaster in their answer you don't list e as a vowel in your list of vowels [ɐɛiouɪɑʊɔ] but in your desired outcome you show ɕ in the third row of the onset column which is only possible if e is part of the vowel list. Can you please edit your question to clarify? ie. add e as a vowel if it was an oversight, or remove ɕ from the desired result.
    – Blindspots
    Jan 9 at 16:58

2 Answers 2


Formula in B2

Single Row, Multiple Formulas

=JOIN(", ", 
       "(\.[ˌˈ]?)([^ɐɛiouɪɑʊɔ])", "$1($2)")))

Single Formula, Multiple Rows

  JOIN(", ", 
      REGEXREPLACE(r,"(\.[ˌˈ]?)([^ɐɛiouɪɑʊɔ])", "$1($2)")))))
1 Strings Results
2 bɐ.ˈɾi.ɕiɾ ɾ, ɕ
3 ˈbɑː.tiɾ t
4 ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe b, t, ɕ


  • REGEXREPLACE captures two groups $1 and $2:
    1. $1 is a . followed by zero or one characters from [ˌˈ] that precede $2
    2. $2 is any character that follows $1 that is not in the exclusion list [^ɐɛiouɪɑʊɔ].
    3. The replacement expression $1($2) creates a string.
      For example, if the original text is ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe then it will become ˌbɛs.(b)ɛ.ˈ(t)ɾɑɪ̯.(ɕ)e
  • REGEXTRACT uses the result from the previous step as a regular expression. So if the previous result was ˌbɛs.(b)ɛ.ˈ(t)ɾɑɪ̯.(ɕ)e it returns btɕ
  • JOIN adds a comma followed by a space as the delimiter making the final result b, t, ɕ as in B4 above.

This will only catch the ɾ and ɕ within bɐ.ˈɾi.ɕiɾ but throw a #N/A for the rest.

Repeating the regular expressions can work. But since we need to match the entire expression, we need to know the exact number of times .[Non-vowel][vowel] is present and therefore the number of times the regex needs to be repeated. Here, I use REDUCE to loop over a SEQUENCE of 10 numbers(in reverse), and each number in the sequence is used to REPeaT the regular expressions. IF it(10) ERRORs out, we try the next number of repetition(9), if not we keep the current one. We do this in a loop and therefore keep the maximum number that doesn't error.

Word Onset 2
bɐ.ˈɾi.ɕiɾ ɾ ɕ
ˈbɑː.tiɾ t
ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe b

Note that e in ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe is not a vowel( in list [ɐɛiouɪɑʊɔ]) and therefore, ɕ is not extracted.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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