Insert delimiter between uppercase and lowercase characters in string

Hi All,

I'm trying to split this string in a column into two columns based on the Case - I've been looking for a while to figure out how to do this? I figured add a delimiter checking for case and use a text to columns function - but now I'm not sure how to add the delimiter based on Case. Here's a sample of what I want

Col 1	Col 2	col 3
ABCaaa	ABC	aaa
AHCNaaarty	AHCN	aaarty
BGhnf	BG	hnf

Thanks - appreciate your help in advance!

Regex

Parse

Preparation

Accepted answers

MarqueeCrew

There is a regex tool that you can use to parse. But as a step,

Regex_replace([string], "(\u+)(\l+)",'$1|$2',0)

Should work. Else switch to ,1. I forget which way the case sensitivity works.

Cheers,

Mark

MarqueeCrew

@shivraj_r,

That is quite a challenge. The string "SETTINGSSettingsRootView" looks like CAPS followed by anything where one would not immediately know if the pair of "S" characters are part of the same word/values. I was afraid that I could not solve the problem for you with Alteryx. I have however conquered that fear by breaking down the problem.

Pattern #1 we have already identified: A set of CAPS followed by lowercase. Now Pattern #2 is CAPS followed by MixedCase.

We can use a REGEX_MATCH() function to see which pattern we are dealing with and write different expressions for each case.

Here is a solution formula:

IF 
     REGEX_Match([Field1], "\u+\l+\u+.*",0) THEN   REGEX_Replace([Field1], "(\u+)(\u\l+.*)", '$1+$2',0)
ELSE
     REGEX_Replace([Field1], "(\u+)(\l+)", '$1+$2',0)
ENDIF

We check to see if the pattern is: 1+ Uppercase followed by 1+ Lowercase followed by 1+ Uppercase followed by anything (with case sensitivity set). Then we apply a replacement formula where we take the first Uppercase group of letters (prior to the first uppercase followed by a lowercase) and assign that to group #1. We add a + sign (as you used it for a delimiter in your example) and place the balance of the expression into group #2.

Otherwise, we use our proven expression.

I love Alteryx and am very fond of RegEx.

Cheers,

Mark

All comments

MarqueeCrew

Hint

\u is uppercase
\l is lowercase.

\u+ is 1 or more uppercase letters
\l+ is 1 or more lowercase letters.

(\u+)(\l+) is 2 groups

Cheers,
Mark

shivraj_r

Thanks @MarqueeCrew

Not sure I follow though - (\u+)(\l+) as a REGEX input? I'm trying to insert a delimiter to split the columns - What do I need to add to check for case and then split the columns?

Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703