Convert string manipulation formula from excel to CR

[Former Member]

Hello everyone,

I have a formula that I am trying to convert to crystal syntax. In excel formula works. Basically, it outputs the string between two hyphen characters.

XXXXX-WHITE-1 >>>>>>> formula >>>>>>>> WHITE

Here is the formula that I use in excel:

=MID(A1,FIND("-",A1)+1,(FIND("-",A1,FIND("-",A1)+1))-FIND("-",A1)-1)

How can I convert this formula into something that crystal reports understand?

P.S. A1 is going to be ItemCode.

Any help is appreciated!

[DellSC · Accepted solution]

Replace the "FIND" with "InStr" and switch the parameters around.  So, it might look like this:

MID({MyField},InStr({MyField},"-")+1,(InStr({MyField},"-",InStr({MyField},"-")+1))-InStr({MyField},"-")-1)

-Dell

[Former Member · Accepted solution]

I have tried that formula already and it did not work.

This is the error I get:

String length is less than 0 or not an integer.

[DellSC · Accepted solution]

Try this:

If not IsNull({MyField}) and InStr({MyField}, "-") > 0 then

MID({MyField},InStr({MyField},"-")+1,(InStr({MyField},"-",InStr({MyField},"-")+1))-InStr({MyField},"-")-1)

-Dell

[Former Member · Accepted solution]

I still get the same error.

[abhilash_kumar · Accepted solution]

Hi Korhan,

Try this please:

local stringvar s := {A1};

local stringvar temp := Mid(s,Instr(s,'-')+1);

if instr(temp,'-') > 0 then

    Mid(temp,1,Instr(temp,'-')-1);

-Abhilash

[Former Member · Accepted solution]

This works. Thank you.