How to parse/replace/delimit an ellipsis (...) from text.

[MervynClarke]

Hello Everyone! 

I've hit a roadblock in my workflow when trying to parse an ellipsis. Can you help?

I've tried the text to columns tool, regex, and a few functions in the formula tool. Nothing I've done has worked...its like the ellipsis is a special character and is ignored by my attempts. 

Here is a basic example. I need to split the text "blahblahblah...end" into two different fields. So it would look like blahblahblah in one field and end in the other. 

Real example text:

"The United States and Croatia have completed negotiations on a new tax treaty, a Treasury Department official said. We are pleased to report that in March, we concluded substantial substantive negotiations with Croatia on a new tax treaty, Kevin Nichols, international tax counsel with the Department of the Treasury’s Office of Tax Policy, told attendees of a virtual conference sponsored by the D.C. Bar earlier this month. He added that the negotiated text, which follows closely from the 2016…Louisiana—Corporate Income Tax: Net Operating Loss Guidance Issued (May 19, 2022) "

Everything within the quotes is an alert I use for work. Its two separate statements. One ends at 2016. The next starts with the state of Louisiana. 

[IraWatt]

Hey @MervynClarke,

Your right '.' is a special character. The dot normally means any character so to get round this you need to use the escape character which is the backslash '\' before it to just make it a nomal '.' like this: '\.'

So for instance in your example you can do something like this:

Here's the regex the backslash allows it to be registered as a '.'

(.+)\.\.\.(.+)

[DataNath]

I think I’ve noticed that before even when just typing in word etc that it becomes something other than simply 3 dots when you type an ellipsis. For the sake of being exact, I just copied and pasted it between two capture groups and the following seemed to work in Regex101. See how you get on using this with the RegEx tool in parse mode:

(.+)…(.+)

[IraWatt · Accepted solution]

Interestingly in the real text the ... is one character which is very weird

I used this regex instead

(.*)…(.*)

[apathetichell]

https://www.compart.com/en/unicode/U+2026

\u2026

Can't get it to work so readily in perl regex (ie Alteryx Regex) - but hypothetically you should be searching for that character.

[MervynClarke]

Thanks this worked for me! It only worked as a V_WString, in my case! Was driving me nuts. Your help is very much appreciated. 

[IraWatt]

Glad you got it sort @MervynClarke ! :)