Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HELP extracting text from a text field - CR11

Status
Not open for further replies.

jeffm777

IS-IT--Management
Nov 10, 2009
108
US
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.


 
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},"-"." ");
 
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},"-"," ");
 
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?
 
Where {@part-IC_fron_text} is

mid({Sheet1_.Text},44)
 

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.

 
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.
 

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.
 
brian...not to worry. all help is good. i have a tendency to make the simple complex.
 
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.

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top