extracting string with ## delimiter

[lumjingbki]

Hi can someone help to extract the below output from the "Data" field. I used "Text to columns" tool but it is giving breaking the link from single # and not recognizing ##

Data	Output
ABCD Hello#123456BILLNO#123456##12345678#231456	##12345678
XYZE HI#125475634 BILLNO#321456##3214567	##3214567
GOAL HI  ##564789124BILLNO#213458	##564789124

[alexnajm · Accepted solution]

Try this Formula: RegEx_Replace([Data],".*(\#\#+\d+).*","$1")

[ArnaldoSandoval · Accepted solution]

Hi @lumjingbki 

Another way to do it, with a RegEx tool.

hth

Arnaldo

[lumjingbki]

@alexnajm @ArnaldoSandoval  thank you for the help

[lumjingbki]

@ArnaldoSandoval @alexnajm thank you for the quick help. I have another type of data set that starts with string after the ## please see below. I am trying to explore but not getting quick solution please help

Data	Output
ABCD Hello#123456BILLNO#123456##12345678#231456	##12345678
XYZE HI#125475634 BILLNO#321456##3214567	##3214567
GOAL HI  ##564789124BILLNO#213458	##564789124
GOAL HI  ##country 564789124 BILLNO#213458	##country 564789124

[ArnaldoSandoval · Accepted solution]

Hi @lumjingbki 

Please use this Regular Expression in the RegEx tool:

.*(##[a-z]*\s*\d+).*

Here its interpretation

.*	Skip from zero to n characters prior to the token.
(##[a-z]*\s*\d+)	Token, selecting data
.*	Skip remaining data
Token	##[a-z]*\s*\d+
##	include two #s
[a-z]*	Any lowercase text, from zero to n-characters
\s*	Zero to n-spaces
\d+	One or more digits

Hope this helps,

Arnaldo