HELP extracting text from a text field - CR11

[jeffm777]

Using CR11...have a text field that I need to extract a section of data out of. Below is an example of the text field as well as an example of the data I need to extract.

Text Field Example 1
CONVERTED Part Sale... Yard# 1 Store# 1 U 601-58797 Retl$ 50.00 Qty 1 Stock# 5895 Core$ 0.00

Text Field Example 2
CONVERTED Part Sale... Yard# 1 Store# 1 U 601-58829B Retl$ 50.00 Qty 1 Stock# 6105 Core$ 0.00

Text Field Example 3
CONVERTED Part Sale... Yard# 1 Store# 1 U 617-00002BR Retl$ 40.00 Qty 1 Stock# NOF Core$ 0.00

What I need to extract is the three numbers before the hyphen and anything after the hyphen up to the first space so my results will look like...

Extracted Example 1
601-58797

Extracted Example 2
601-58829B

Extracted Example 3
617-00002BR

Thanks in advance for any assistance.

 

[fisheromacse]

I do not have crystal in front of me, but one of the below should help you.

//{@takemiddle}
numbervar hyp := instr({table.field},"-")-3;
numbervar spa := instr({table.field}," ");
MID({table.field},hyp,spa)



OR


//{@takemiddle2}
extractstring({table.field},"-"." ");

 

[jeffm777]

The first formula is returning the following error..."Start position is less than 1 or not an integer". I used the following formula....

//{@takemiddle}
numbervar hyp := instr({Sheet1_.Text},"-")-3;
numbervar spa := instr({Sheet1_.Text}," ");
MID({Sheet1_.Text},hyp,spa)

The second formula returned a value but it was everything after the hyphen. I used the following formula....

//{@takemiddle2}
extractstring({Sheet1_.Text},"-"," ");

 

[jeffm777]

On your second formula, I could do another formula on the same text field like...

left({@part-IC_from_text},4)

and then concatenate them back together to give me my results. Do you see an easier way?

 

[jeffm777]

Where {@part-IC_fron_text} is

mid({Sheet1_.Text},44)

 

[briangriffin]

I don't want to step on fisheromacse's formula, that's probably exactly how I would do it, but just for kicks this would also work:

whileprintingrecords;
stringvar array v_array := split({YourTextField}," ");
stringvar v_display;
numbervar x := 1;

while x <= ubound(v_array)

do
(v_display := v_array[x];
if isnumeric(replace(v_display,"-","")) = true and len(v_display) >= 7 then x := ubound(v_array) + 1
else
x := x + 1);

v_display


This will return the first string of 7 or more characters that is all numeric other than dashes. The advantage might be if someone entered a dash somewhere in the string before the one that you really want.

 

[jeffm777]

Thanks Brian. That works but the results aren't always going to be numeric. Some will end with one or two characters so it's returning incorrect data on those.

 

[briangriffin]

I see that now in your test data - sorry. Back to the original approach:

whileprintingrecords;
numbervar v_start := instr({@text},"-");
stringvar v_display := mid({@text},v_start-3,20);
numbervar v_end := instr(v_display," ");

v_display := left(v_display,v_end - 1);

v_display


And although it's unlikely that the value you want will always be in the 10th position:

stringvar array v_array := split({@text}," ");

v_array[10]

might also work for you.

 

[fisheromacse]

brian...not to worry. all help is good. i have a tendency to make the simple complex.

 

[briangriffin]

Isn't that why you're in management?

 

[fisheromacse]

LOL - funny you should say that!

My managing skills have been honed by spending more time explaining to staff why you can't receive more than 1 fax at the same time than i ever thought would be possible!

I chose 'Management' because i am the IT dept where i work. So i am the programmer, the developer, the deployer, the helpdesk, the manager, the guy who can reach the top shelf with the heavy box, etc.

 

[lbass]

If the field is always in this format, and if the ellipses in your example are actually in the field, then you should be able to use something like this:

stringvar array x := split({table.field}," ");
if ubound(x) >= 9 then
x[9]

-LB