filter - How can I extract the numeric portion before the first letter in a FileMaker calculation? - Stack Overflow

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?

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?

• filter
• character
• numeric
• filemaker
• letter

Share Improve this question Follow asked Jan 31 at 19:45 KenKen 311 silver badge11 bronze badge

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

Filemaker has no native support for RegEx, so the term [A-Z] is meaningless.

I would suggest something like:

Let ( [
stripped = Replace ( Serial Number ; 1 ; 6 ; "" ) ;
letters = Filter ( stripped ; KanjiNumeral ( stripped ) ) ;
pos = Position ( stripped ; Left ( letters ; 1 ) ; 1 ; 1 ) 
] ;
Replace ( stripped ; pos ; Length ( stripped ) ; "" )
)

Alternatively you could install the (free) BaseElements plugin and do:

BE_RegularExpression ( Serial Number ; ".{6}(\d+).+" ; "" ; "$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 )
)

Here's a custom function that extracts from the collected string the series of consecutive numeric characters found from the starting position. Paste this custom function into your solution and call it like this

tText_00ExtractConsecutiveNumbers ( Serial Number ; 7 )

/**
tText_00ExtractConsecutiveNumbers ( stringToTreat ; startingPosition ) 
Version : 1.0
**/


While ([ 

startingPosition = Abs ( Int ( GetAsNumber ( startingPosition ) ) ) ;
stringLength = Length ( stringToTreat ) ;
maxLoop = If ( startingPosition ≤ 1 ; stringLength ; stringLength - (startingPosition - 1 ) )  ;
isTheEnd = False ;
ctr = startingPosition 

]; 

not isTheEnd

;[

isANumber = not IsEmpty ( GetAsNumber ( Middle ( stringToTreat ; ctr ; 1 ) ) ) ;
isTheEnd = ( ctr > stringLength ) or ( not isANumber ) ;
resultToReturn = Middle ( stringToTreat ; startingPosition ; ctr - startingPosition ) ;
ctr = ctr + 1 
    
];

resultToReturn

)