topic Re: Parse if Capital Letter in Middle of Word in Alteryx Designer Desktop Discussions

Parse if Capital Letter in Middle of Word

kristinaheuer — Wed, 12 May 2021 14:47:13 GMT

Hi -

I am trying to improve a customer file - I know I won't be able to solve for many cases, but I am trying to parse names if there is a capital letter within the name. Example: SteveR I want to parse into Steve (column 1) and R (column 2). Below is a snippet of my data set for reference.

Re: Parse if Capital Letter in Middle of Word

MarqueeCrew — Wed, 12 May 2021 14:53:17 GMT

What about McMillan or HelenMcMillan?

Re: Parse if Capital Letter in Middle of Word

kristinaheuer — Wed, 12 May 2021 14:55:04 GMT

Since this dataset is "first names" only, I am mostly concerned with grabbing the true first name and remove middle initials and middle names.

Re: Parse if Capital Letter in Middle of Word

apathetichell — Wed, 12 May 2021 15:51:01 GMT

try regex_match([column],"\w+\u+\w*",0") - that worked with my dataset.

I'd expect REGEX_Match([test],"[a-zA-Z]+\u+\w*",0)

would be more precise and should be better but I'm not seeing differences on my sample data.

Sorry - misread your request - those would just grab which columns have a non-leading capital letter inside the field.

I'd do something like (\u*\w+)(\u+\w*)|(\w+) in regex tool (parse) case insensitive unchecked.

Re: Parse if Capital Letter in Middle of Word

MarqueeCrew — Wed, 12 May 2021 15:10:21 GMT

@kristinaheuer ,

I'd use a RegEx tool set to PARSE the [firstName] with case insensitivity option UNCHECKED

(\u\l*)(\u*\l*)

cheers,

mark

Re: Parse if Capital Letter in Middle of Word

AdamR_AYX — Wed, 12 May 2021 15:54:14 GMT

I have a non regex solution, but I'm not convinced it is any clearer to read than the regex answers in this case

FindString(Substring(ReplaceChar([Field1], "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "*"),1),"*")+1

gives you the index of the first capital letter not counting the first character of the string.