Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extract certain text in string with formula tool

dlopez
8 - Asteroid

Hi Community!

 

Looking to extract certain text out of a string value with the formula tool, I'm thinking RegEx, to create two fields

 

Current State: 

 

USA  10 041 South Central

 

Desired State: 

New Field 1 = 10

New Field 2 = 041 South Central 

 

Any help would be great. 

 

Thanks in advance!

 

 

8 REPLIES 8
messi007
15 - Aurora
15 - Aurora

@dlopez,

 

Below the RegEx

 

messi007_0-1614102853889.png

Attached the workflow,

 

Regards,

PhilipMannering
16 - Nebula
16 - Nebula

Hi @dlopez 

 

As well as Messi07's solution, you could also use Text-to-Columns with a space (or \s) as the delimiter and Number of columns = 3.

dlopez
8 - Asteroid

@messi007  & @PhilipMannering 

 

Thanks for the tips, would it be possible to do this with a formula in the formula tool? 

 

Cheers

Philip
12 - Quasar

 I'm curious why the Formula tool? It can be done with Regex, substring, substring with find. Lot's of options. To me, though, the Text To Columns is the simplest and most straightforward way given the example data you provided. And would be the simplest to document, maintain, and understand.

dlopez
8 - Asteroid

@Philip 

 

Yah completely agree, not denying all those tools can do it, they certainly can. I typically don't like to add more tools to my flow than necessary, I try to keep it as lean/simple as possible, I already have a formula tool in my flow which is why I'm looking for a solution in that tool specifically; and I just utilized a substring() function to do this. 

 

Cheers

PhilipMannering
16 - Nebula
16 - Nebula

@dlopez 

 

Here's a formula tool way of doing it attached.

 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@dlopez ,

 

I'm piggy-backing on @PhilipMannering :

 

Expression #1: [FIELD 1]

Getword([Field],1)

 

Expression #2: [FIELD 2]

Substring([field],length(GetWord([Field], 0)) + length([Field 1]) + 2)

 

Cheers,

 

Mark

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
dlopez
8 - Asteroid

@PhilipMannering & @MarqueeCrew 

 

Thanks for the solutions, always enjoy learning new ways of doing things. Below you'll see my solution I came to....

 

Current Field = USA  10 041 South Central - all records have "USA" before the bits I need

 

Calc Field 1 = Trim([Current Field], "USA") - produces "10 041 South Central"

 

Calc Field 2 = Left([Calc Field 1], 3) - produces "10"

 

Calc Field 3 = Substring([Calc Field 1],4) - produces "041 South Central"

 

I also have a SELECT tool in my flow, so I just drop calc field 1 there and leave calc field 2 & 3. This allows me to not have to add any new tools, already have a SELECT in my flow to rename fields, and keep the flow slim and trim. 

 

Again thanks for your feedback on this, the GETWORD() func is a new one to me that I'll have to play with.

 

Cheers

Labels