Mid/find formula not operating as expected

Hello! I am trying to replicate the Mid/find formula combo in excel to parse through a text string

Here's some example text, I want my new column to give me the 0301 from this text:

Total.0301 - Example Example

So I'm using the substring/findstring combo as such:

( Substring ( [ mydata ] , FINDSTRING ( "." , [mydata] ) + 1, 5 ) )

So I would expect this to look for the "." and pull the 5 characters after that.. but alteryx is just pulling the first 5 characters of my data instead.

Any idea of what I'm doing wrong?

Workflow

Developer

Common Use Cases

Accepted answers

Jonathan-Sherman

Hi @kelseyfalls,

You could use the following formula: REGEX_Replace([Text], '.*?(\d+).*', '$1')

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200505 - ExtractString.yxmd

All comments

Jonathan-Sherman

Hi @kelseyfalls,

You could use the following formula: REGEX_Replace([Text], '.*?(\d+).*', '$1')

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200505 - ExtractString.yxmd

JosephSerpis

Hi @kelseyfalls this is the syntax you would need if you were using Findstring and Substring "Substring([Text],FindString([Text], ".")+1,4)".

kirans666

I believe you have got the items in the find function reversed , the below should give you the desired results.

(Substring([Field1] ,FINDSTRING ([Field1],".")+1, 5 ) )

kelseyfalls

Thank you! This worked beautifully!

kelseyfalls

So what if you also have data like this in your grouping?

Example2: Example_EU2.0130 - Example

I want a formula that will extract the 0130 from this one and the first example: Total.0301 - Example..

Is that possible? The first formula just extracted the 2 from my first example which isn't what I want, but these are sort of two different patterns so I'm not sure it can be done.. maybe there's a way to do an IF function afterwards to identify the ones that didn't work?

kirans666

You can use this to get any numbers after the "."

trim((Substring([Field1] ,FINDSTRING ([Field1],".")+1, 5 ) ))

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167