Extract segment from string

[UHsoccer]

I am trying to extract the third table entries such as

salesDes = "Sold to 1234567, products ordered XX4567 " or
salesDes = "Sold to 456" or
salesDes = "Sold to 54321,none delivered" etc

I need the number 1234567, 456, 54321. It is followed by a space or a comma

Found a reference to this problem with the following formula.

replace(Split(salesDes," ")[3], ",","")

More is needed because it expects an array, but what?

 

[MJRBIM]

Is the leader for salesDes ALWAYS = "Sold to "...?

 

[UHsoccer]

Sold to is always the leader and the number is the third entry followed by a space OR a comma

 

[WarrenRoss]

If "sold to " is always the leader you can use the following formula:

//some variables to keep the formula readable
local numbervar nSpaceIndex;
local numbervar nCommaIndex;
local numbervar nIndexToUse;

//find the first spaces or commas, after the "sold to " bit
nSpaceIndex := instr(9, {data2_txt.stringval}, ' ');
nCommaIndex := instr(9, {data2_txt.stringval}, ',');

//pick the smaller of the two, as that's the one we want
nIndexToUse := IIF (nSpaceIndex > nCommaIndex, nCommaIndex, nSpaceIndex );

//if the index is 0, no spaces or commas were found so we need the whole string
if nIndexToUse = 0 then nIndexToUse := length({data2_txt.stringval}) + 1;

//grab the value
mid({data2_txt.stringval}, 9, nIndexToUse - 9);

 

[UHsoccer]

Thanks WarrenRoss, that did the trick.

Appreciate the help

 

[UHsoccer]

This worked untill .....

Discovered that the string can ALSO be like this

Sales number 123, sold to 789

So I tried to find the location where the "Sold to" starts (soldIndx) and use that in the formula. Then I changed the "9" to (soldIndx+1) I was greeted subscript errors.

In addition I was told that the separator character might also be a minus sign. That affects the "nIndexToUse := IIF " statement big time

Appreciate your help

Here is my current code

whileprintingrecords;

local numbervar nSpaceIndex; //some variables to keep the formula readable
local numbervar nCommaIndex;
local numbervar nMinusIndex;
local numbervar nIndexToUse;

local numbervar strLen;
local numbervar strSoldTo;
local stringvar strVar ;

strVar := {INCAT$Sales_Invoice_Line.Description};
strSoldTo := instr(1, lcase(strVar), 'sold to') ;
strLen := Length (strVar) ;
if strLen > 0 and strSoldTo > 0 then (

nSpaceIndex := instr(9, strVar, ' '); //find the first spaces or commas, after the "sold to " bit
nCommaIndex := instr(9, strVar, ',');
//nMinusIndex := instr(9, strVar, '-');

nIndexToUse := IIF (nSpaceIndex > nCommaIndex, nCommaIndex, nSpaceIndex); //pick the smaller of the two, as that's the one we want

if nIndexToUse = 0 then nIndexToUse := length(strVar) + 1; //if the index is 0, no spaces or commas were found so we need the whole string

mid(strVar, 9, nIndexToUse - 9); ) //grab the value

else "" // Display blank