SOLVED
extracting string with ## delimiter
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
lumjingbki
8 - Asteroid
‎06-29-2023
05:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
Labels:
- Labels:
- Developer
5 REPLIES 5
18 - Pollux
‎06-29-2023
05:24 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Try this Formula: RegEx_Replace([Data],".*(\#\#+\d+).*","$1")
ArnaldoSandoval
12 - Quasar
‎06-29-2023
05:35 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
‎06-29-2023
05:50 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@alexnajm @ArnaldoSandoval thank you for the help
‎06-29-2023
05:58 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
12 - Quasar
‎06-29-2023
07:08 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
