Extraction Formula Help

Experts:

Can you tell me the formula to use in order to extract the characters found in the SECOND TO THE LAST number in this type of string?

N_1051010_98_822
N_1050815_2_14


In this above example I need to extract the "98" and the "2" in the above 2 examples. It would always be the number (1-3 digits) immediately BEFORE the last "_"

What I will more than likely needing to do is LINK one table that contains this NUMBER to another table that has the number is a more normal format of 98.

ie.

TABLE 1

DATE          CODING
01/01/05     N_1051010_98_822
01/05/05     N_1050815_2_14

TABLE 2

DATE          BATCH
01/01/05      98
01/05/05       2

I'm needing to LINK TABLE 1 (CODING "Extract" number ie. 98) with TABLE 2  BATCH number field.

How can I first of all extract the number, then if possible use the extract to LINK the two tables???

Thanks
Mike

LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
bdreed35Commented:
A slightly simpler formula would make use of the split function:

local stringvar array batch := split({table.field},"_");
batch[ubound(batch)-1]
0
 
PWinterCommented:
Hi.

InStrRev (your_string, "_")
returns position of the last underscore.

InStrRev (your_string, "_", InStrRev (your_string, "_") - 1)
returns position of the second to last underscore.

Mid(your_string, InStrRev (your_string, "_", InStrRev (your_string, "_") - 1) + 1, InStrRev (your_string, "_") - InStrRev (your_string, "_", InStrRev (your_string, "_") - 1) - 1)
returns what you need. :)

Thank you,
PW
0
 
mlmccCommented:
Both are good answers.

mlmcc
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thank you...I will use the above suggestions.... this project has been set back, but I can use the above suggestions.

THANKS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.