7 Replies Latest reply on Jul 12, 2017 12:11 PM by TomHays

Calculation question

This should be an easy one for many of you- but I'm not sure how to proceed.

I have item numbers in my database that are 5-6 numbers followed by two letters- e.g. 685499DG.  I use a calculation to isolate the last 2 letters in a field called BrandCode.

Problem is, we just introduced new item numbers that have only 1 letter at the end- eg. 685499R and for those items the Brand code is "R".

I need a formula that basically looks for the letters at the end- wether it's 1 letter, 2 letters, etc.

Thank you!

• 1. Re: Calculation question

If there are never letters anywhere but at the end and they are always upper case:

Filter ( YourField ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

1 of 1 people found this helpful
• 2. Re: Calculation question

Filter ( Upper ( Field 11 ); "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

im sure there are better ways...

*edit*  sorry phil   I guess I was responding while you posted.

1 of 1 people found this helpful
• 3. Re: Calculation question

This is on the those "cats" that can be "skinned" any number of ways. Much depends on how many different ways the data might vary from record to record--which we can't see from a single sample.

1 of 1 people found this helpful
• 4. Re: Calculation question

Thanks for the reply..  they can be both upper or lower.  Is your calc case sensitive?

would I need to add all the lowercase letters as well?

• 5. Re: Calculation question

The following calculation will locate all non-numbers following the last number in the string for the field YourField.

If there are no numbers, it will return the entire string.

Let([

theInput = YourField;

_n = GetAsNumber(theInput);

_last_digit = Right(_n; 1);

posn_last_digit = Position(theInput, _last_digit, Length(theInput),-1)

];

Right(theInput; Length(theInput) - posn_last_digit )

)

685499DG --> DG

685499R --> R

AX32154M32XXX --> XXX

234rAb --> rAb

ABCD --> ABCD

-Tom

• 6. Re: Calculation question

the filter function is sensitive , but you can use the "upper" or "lower"  to control the format you want.

i.e.   the original field  can be case insensitive, but your calculation to remove numbers can be set to either or

1 of 1 people found this helpful
• 7. Re: Calculation question

On further study, I found that the behavior of GetAsNumber() was not quite suitable for this calculation.

My previous calc failed on "234.0dog".  It returned ".0dog" instead of "dog".  (GetAsNumber() doesn't recognize .0 as part of the number since it doesn't seem to like the trailing zero. It sees "456.1dog" well enough.)

Here is a revised calculation using Filter() that works for all the previous input value formats and

"234.0dog" --> dog

Let([

theInput = YourField;

_n = Filter(theInput; "0123456789");

_last_digit = Right(_n; 1);

posn_last_digit = Position(theInput; _last_digit; Length(theInput);-1)

];

Right(theInput; Length(theInput) - posn_last_digit )

)

-Tom