topic Re: How to create a formula column that extract letters/numbers from an ID? in Discussions

How to create a formula column that extract letters/numbers from an ID?

Danial1 — Sun, 11 Jun 2023 11:09:22 GMT

I have a list of ID numbers. However, I only number which is important to me is the last five digits from the ID number. I'm sure there's a formula column script we could utilize to perform this.

For example:

The id number that i have is 24KL-L200123. I just need to create a formula column that could pull the last 5 digits and the end result would be 00123.

Thanks. sincerely appreciate any advise on this

Re: How to create a formula column that extract letters/numbers from an ID?

jthi — Mon, 21 Dec 2020 09:42:20 GMT

Are the ID numbers always similar format to your example? As in are the last five characters in the string always digits you want? If they are you could use Right() function.

Right("24KL-L200123",5);

Re: How to create a formula column that extract letters/numbers from an ID?

HadleyMyers — Mon, 21 Dec 2020 09:42:50 GMT

Hi,

Does this work for you?:

Substr( :Original Value, -5, 5 )

Re: How to create a formula column that extract letters/numbers from an ID?

Danial1 — Mon, 21 Dec 2020 10:27:06 GMT

it worked. I'm interested to know what's the fundamental behind this. Could you share them a little in detail. Thank you

Re: How to create a formula column that extract letters/numbers from an ID?

jthi — Mon, 21 Dec 2020 10:49:06 GMT

I suggest checking out JMP Scripting Index (from JMP go to Help / Scripting Index) and https://www.jmp.com/support/help/en/15.2/index.shtml#page/jmp/character-functions.shtml .

Right(string, count:( returns a substring of the right-most count characters of the string.

Substr(string, startIndex, count:( extracts the characters that are the portion of the first argument string. Begins at the position given by the second argument (startIndex), and ends based on the number of characters specified in the third argument (count). If start is negative, Substr searches backward from start from the end of the string. If length is negative or absent, Substr returns a string that begins with start and continues to the end of the text string.

Re: How to create a formula column that extract letters/numbers from an ID?

txnelson — Mon, 21 Dec 2020 13:33:34 GMT

@HadleyMyers response using the Substr() function, is the method that I most frequently use, there is a function that has been added that actually enhances the readability of the code and provides the specific functionality that you need.

Right( :Original Value, 5 )