Mid/find formula not operating as expected

[kelseyfalls]

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?

[Jonathan-Sherman · Accepted solution]

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

[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 ) ))