Split words at capital letters

[MuddyW]

Hello Regexperts,

I have pulled a regex from the internet to seperate words that start with capital letters.  

The regex is here

TEXTJOIN(" ",,REGEXEXTRACT(R158,REGEXREPLACE(R158,"([A-Z][a-z]+)","($1)")))

When I have this: 

HummelfilmTamtam FilmThe Imaginarium Films 

The Regex works and returns this:

Hummelfilm Tamtam Film The Imaginarium Films

But when I have this

Nordisk Film ProductionNadcon FilmZweites Deutsches Fernsehen (ZDF)

or this

Zentropa International SwedenCanal+Ciné+

The Regex breaks.

It looks like the presence of non alphanumeric characters is causing the problem.  Can anyone please help me out and rewrite the Regex so that it works with non alphanumeric characters too?

Edited Monday at 05:31 PM by MuddyW
Clarity

[requinix]

I was following along until the "please do this for me" bit at the end.

REGEXREPLACE + REGEXEXTRACT like that is silly. Not sure where you got it from, but a single REGEXEXTRACT is enough to extract all the <uppercase letter + stuff>s in the cell. Check the documentation/help docs for how to make it match everything instead of just once (which is what it does by default).

For the regex part, it's currently doing <uppercase letter + lowercase letters> so naturally it will only work with lowercase letters and not with numbers or symbols. If you want to match things that look like <uppercase letter + stuff> then realize that "stuff" is a less precise way of saying "keep going until the next uppercase letter". Or in other words, "anything that isn't uppercase". Because computers need you to be precise if you want them to work a certain way. Excel's regex syntax for "anything that isn't ___" is the same as everybody else does it, so you can check either the docs (which I'm sure include a little bit of regular expression syntax) or virtually any other regex resource to find how to write that.