Extract certain text in string with formula tool

dlopez

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!

Tips and Tricks

Accepted answers

philipmannering

@dlopez

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

Extract with formula tool.yxmd

MarqueeCrew

@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

All comments

messi007

@dlopez,

Below the RegEx

Attached the workflow,

Regards,

Reg20210223.yxmd

philipmannering

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

@messi007 & @PhilipMannering

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

Cheers

Philip

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.

philipmannering

@dlopez

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

Extract with formula tool.yxmd

MarqueeCrew

@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

dlopez

@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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703