# 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
###### Who is Participating?

Commented:
A slightly simpler formula would make use of the split function:

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

Commented:
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

Commented:

mlmcc
0

Software 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.