How can I extract the numeric portion before the first letter in a FileMaker calculation?

Question

I have a field called Serial Number in FileMaker, and I need to extract the numeric portion of the string that appears before the first letter (A-Z), after removing the first 6 characters.

Here's the behavior I expect:

1. Remove the first 6 characters from the Serial Number.
2. Extract the numeric part before the first occurrence of a letter (A-Z).
3. Convert the extracted portion into a number.

For example:

• Given the Serial Number value: 15266R16S12P2, the result should be 16.
• Given the Serial Number value: 12345P382M45, the result should be 382.

I've tried using a Let function with Position and Min to find the first letter and extract the digits, but I’m encountering issues where the calculation does not work as expected.

Here's what I thought would work initially (I don't think "[A-Z]" is a valid input for the Position function):

Middle ( Serial Number; 7; Position ( Serial Number; "[A-Z]" ; 7 ; 1 )- 7)

Here’s my most recent attempt:

GetAsNumber (
    Let (
        [
            stripped = Right( Serial Number ; Length( Serial Number ) - 6 ) ;
            firstLetterPos = Min( 
                Position( stripped ; "A" ; 1 ; 1 ) ;
                Position( stripped ; "B" ; 1 ; 1 ) ;
                Position( stripped ; "C" ; 1 ; 1 ) ;
                ...
            ) ;
            firstLetterPos = If( firstLetterPos = 0 ; Length( stripped ) + 1 ; firstLetterPos ) ;
            numericPart = Left( stripped ; firstLetterPos - 1 )
        ] ;
        GetAsNumber( numericPart )
    )
)

Problem:

• The calculation works only when the second letter is a specific letter (e.g., A) and does not correctly handle other letters or edge cases where there are no letters present after the 6th character.

What I'm Looking For:

• I would like to extract the numeric portion before the first letter (A-Z) after removing the first 6 characters, and convert that portion into a number. Could someone help me correct this calculation or suggest a better approach?

Answer 1

Does this work for you?

Let ([
    // test input. Put your real input here:
    ~serial = "12345P382M45" ;

    ~stripped = Right ( ~serial ; Length ( ~serial ) - 6 ) ;
    ~letter = Left ( Filter ( ~stripped ; "qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM" ) ; 1 ) ;
    ~pos = Position ( ~stripped ; ~letter ; 1 ; 1 ) ;
    // I'm not sure what you want to happen if there are no letters
    // but this will return EVERYTHING after the first 6 digits in
    // the absense of letters(~pos == 0)
    ~res = If (
        ~pos > 0 ;
        Left ( ~stripped ; ~pos - 1 ) ;
        ~stripped
    )
];
    GetAsNumber ( ~res )
)