Help Parse Text Calculation

[beckham]

Help Please, I am trying to figure out how to extract field contents via a calculation as follows:

My lookup field is ABCDEFG-01-12345.jpg

I want to automaticaly extract only 12345 to be placed into another field? I tried the Calculation Right ( Sorce field; 5 ) but that returns me 5.jpg

any adivise is most welcome.

Thank you

[Josh Ormond]

Try something like this. Replace the text on the first let variable with your field value.

Let 
( 
[

fileName = "ABCDEFG-01-12345.jpg" ; 

dashCount = PatternCount ( fileName ; "-" ) ; 
periodCount = PatternCount ( fileName ; "." ) ; 

pos1 = Position ( fileName ; "-" ; 1 ; dashCount ) + 1 ; 
pos2 = Position ( fileName ; "." ; 1 ; periodCount ) ; 

result = Middle ( fileName ; pos1 ; pos2 - pos1 )

] ; 

result

)

 

[Lee Smith]

Or RightWords ( "ABCDEFG-01-12345.jpg" ; 2)-1

[Josh Ormond]

I've used that in the past. But there are some weird voodoo rules with what FM counts as a "word". I just avoid that completely if I can.

5 minutes ago, Lee Smith said:

Or RightWords ( "ABCDEFG-01-12345.jpg" ; 2)-1

Actually that calc results in: 12344

Using "- 0" instead also forces it to be a number and does work. Though, you can still get collisions if there are letters elsewhere before the breaking characters.

[Lee Smith]

Is that part of the weird voodoo you were referring, nice catch JO, but I call it user error.

I even put the text into Data View to verify that there wouldn't be any "voodoo" problems.

Well, at least Beckham has another way to look at for a solution. 

[Josh Ormond]

Try this in the data viewer:

RightWords ( "ABCDEFG-0-12345.jpg" ; 1 )

 

Now add an "a" before the period.

RightWords ( "ABCDEFG-0-12345a.jpg" ; 1 )

 

Now add another "a" before the second dash.

RightWords ( "ABCDEFG-0a-12345a.jpg" ; 1 )

 

Now add a number after the "G".

RightWords ( "ABCDEFG1-0a-12345a.jpg" ; 1 )

 

[Lee Smith]

ok, you make a good point,

Filter (RightWords ( "ABCDEFG1-0a-12345a.jpg" ; 1 ) ; 1234567890 ) 

I will never use the RightWords again, really different in the results. Ok, that is a fib, LOL

Always forget the GetValue Function.

GetValue ( Substitute ( "ABCDEFG-01-12345.jpg" ; ["-" ; ¶] ; ["." ; "¶" ]) ; 3 )

Lee

 

Beckham.fmp12

[beckham]

Hi Lee, Thank you for the sample file i am most grateful .. even lloking at the sample file i dont yet understand this witchcraft used in Calc fields but i am able progress my solution further with your generous help...

Thanks again..

[Josh Ormond]

Does the format of the file name ever change? It is it always the same as the example you showed?

[Lee Smith]

Hi Beckham.

Closely verify that your data will parses using one of the calculations. If not, then post a copy of the one(s) that broke.

As Josh pointed out, the pattern of the data is critical.  The more sample data that is provided, the better the chances that our calculations won't brake, as Josh demonstrated with the RightWords Function. 

12 hours ago, beckham said:

i dont yet understand this witchcraft used in Calc fields

Do not feel alone. Feel free to ask us what you don't understand about these calculation. There are resources available that can demystify things such as the Let Function. 

Lee