Left Middle Middle

[grumbachr]

In the he wonderful solution that I've inherited there is a number that is generated in a separate system and can look something like;

ABC 22-01

ABC 22-01SP-R

ABC 333-01

ABC 333-01SP

ABC 4444-01

ABC 4444-01EF

The ABC is a Type the next numbers are a family and the number after the dash are the individual, the rest is added and I'm still not sure why or when it get there but sometimes it there.

In an attempt to clean things up, elsewhere they have created another field that is based on that number but is slightly different it will only be based on the first three strings and has underscores. So the above would look like;

ABC_22_01

ABC_22_01

ABC_333_01

ABC_333_01

ABC_4444_01

ABC_4444_01

I'm trying to write a calculation to automatically insert a value based on the what is first entered.

Here what I've gotten so far.

Left ( Sample #; 3 ) & "_" &

Case (

Middle ( Sample # ; 5 ; 3 ) ≥ 99 < 1000; Middle ( Sample # ; 5 ; 3 ) ;

Middle ( Sample # ; 5 ; 4 ) ≥ 999 ; Middle ( Sample # ; 5 ; 4 ) ;

Middle ( Sample # ; 5 ; 2 )

Since the field is text is my case statement failing? Is there another way to get that number? After I get the middle value to input correctly I have to grab the rest but the middle is were I'm stuck right now.

[comment]

It's not quite clear what you're trying to accomplish here, but two things stand out:

1. x ≥ n < m is not a valid expression; you need to use x ≥ n and x < m;

2. If you substitute the hyphen for a space (or underscore or another full word-breaking character), you can then use MiddleWords() to extract the individual parts, without worrying about their length.

[JesseSFR]

It's definitely tough to discern exactly what you are getting at but, I think what you might be trying do is turn:

ABC 22-01 into ABC_22_01 or

ABC 222-01SP-R into ABC_222_01

and if that's the case I would do exactly what comment says.

let(

  #Change _ into a space

  oldNumber = Substitute( Sample #; "-"; " ");

 

 ##Get the first word and an underscore

  leftWords( oldNumber ; 1 ) & "_" & 



 ##Get the second word and an underscore

  MiddleWords( oldNumber ; 2 ; 1 ) & "_" &



 ##Get the third word and filter numbers

  filter( MiddleWords( oldNumber; 3; 1 ) ; "0123456789")

)

Now if there happens to be any more numbers after the text values such as:

ABC 444-01SP3-R

your new calc would look like:

ABC_444_013

I think that is about what you are looking for though. Clarify a little bit and we might be able to help some more.

[comment]

I would do exactly what comment says

Ahem. I DIDN'T say you need to substitute the underscore. I said you need to substitute the hyphen. The underscore is always a word-delimiter, while the hyphen is not - see:

http://fmforums.com/forum/showtopic.php?tid/169007/post/172323/#172323

http://fmforums.com/forum/showtopic.php?tid/169668/post/175060/#175060

[JesseSFR]

OOPS! Fixed the previous post... thanks for pointing that out comment...