Solved

# Extraction Formula Help

Posted on 2006-03-21
526 Views
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

0
Question by:MIKE

LVL 6

Assisted Solution

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

LVL 28

Accepted Solution

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

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

LVL 100

Expert Comment

mlmcc
0

LVL 17

Author Comment

Thank you...I will use the above suggestions.... this project has been set back, but I can use the above suggestions.

THANKS
0

## Featured Post

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…