Isolate email addresses from a column using Magic ETL or BeastMode
Hello, I am trying to isolate email addresses from an Information column in a Dataset. A sample of the data from the column would be something like 'someone's name [email@email.com]'
I don't necessarily need the name part, but I do need the Email Addresses, which are always bracketed in this column by '[]'.
I am using a Split Column tile in Magic ETL, and in the Choose Delimiter to Split On section I have 'Custom' selected and using the following REGEX code that I'm sure is wrong:
REGEX_EXTRACT([Information], "\[(.*?)\]", 0)
The code compiles, but the newly created column is copying all the text from the Information column (Column that's being split) exactly.
Any ideas how I can only grab the email address that's inside the brackets? I'm sure I'm missing something.
Thank you!
Best Answer
-
REGEX can be tricky, specially when it comes to replacing, so you might want to try a couple of different options until you find one that works, here's one you can try:
REGEXP_REPLACE(`Information`,'.*([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+).*','$1')
1
Answers
-
it’s not matching anything so it’s returning the original string. You can use a formula tile and REGEXP_REPLACE
REGEXP_REPLACE(`Information`, '^.*\[([^\]+).*$', '$1')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
alternatively you can split part the split part
SPLIT_PART(SPLIT_PART(`Information`, '[',2), ']', 1)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
For some reason the Formula Tile gives me an Unknown Error Occurred when I try REGEX in the formula bar. That's where I tried it first.
0 -
Sorry @blittle - Appears it didn't get adequately submitted. I've updated the formula in the comment above.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hi Grant, Thank you for the help, I appreciate it!
I tried the amended formula you posted, and 'validate formula' seems to like it, but when you run the preview, I am getting a new error.Any thoughts?
Thanks again!
0 -
REGEX can be tricky, specially when it comes to replacing, so you might want to try a couple of different options until you find one that works, here's one you can try:
REGEXP_REPLACE(`Information`,'.*([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+).*','$1')
1 -
Wow @marcel_luthi, that's definitely closer than I've been! That did semi work. I'm terrible with REGEX, so it's all trial and error for me.
It seems to be grabbing the email address, but only returning the last character of the person's name, the @ symbol and domain (example: S@gmail.com). So it appears to be looking at what's inside the brackets, but only grabbing a portion.Is it only grabbing a character count of 1 to the left of the '@' symbol?
0 -
I think I got the following to work…
REGEXP_REPLACE(`
Information`
, '.?([a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+.[a-zA-Z0-9_-]+).', '$1')It seems to be returning the full email address after an initial test, but I need to open up my filter to look at more rows.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive