Formula conversion

[pglover]

Can someone help with Crystal syntax please?

I know how to in Excel [=UPPER(RIGHT(A1,(LEN(A1)-SEARCH(".",A1))))] but can't find the right pointers in the help file in Crystal.

I need to strip an unknown number of characters to the left of, and including, the full stop;

so
pglover.helpdesk.it.london.company
becomes
helpdesk.it.london.company

Can it be done in a formula?

Many TIA

Penny

 

[IanWaterman]

Penny

Had to do this as part of my accreditation exam 2 years ago, there was not a formula or UFL to do it then so there might be now. I resolved it by doing the following.

Local numbervar i:= 1;
Local numbervar counter:= length{Fieldname};
Local Stringvar Display :="";
Local NumberVar Fullstop :=0;

For i = 1 to counter do
If Full stop = 0 then
(If {Fieldname} = "." then Fullstop :=1 else 0) else
If fullstop = 1 then
display:= display + {Fieldname};
display;

This should now check through your string field until it encounters a full stop then start building the dislay string. The next full stops will be ignored as the first one set the var fullstop to 1

Ian Waterman
UK Crystal Consultant

 

[pglover]

Thanks Ian

Can I ask what is likely to be a question that is probably obvious to most? I use this in the formula? Substitue the fieldname for the appropriate field?

 

[IanWaterman]

Just create a new formula as above, and insert your fieldname containing your address in the locations I have described as {Fieldname}.

Sorry just spotted a typo this line should read

(If {Fieldname} = "." then Fullstop :=1 else 0) else
If fullstop = 1 then

I missed out the in the original.

Ian

 

[pglover]

Ian

Was the typo the Full stop (should be Fullstop)? Cos I can spot no difference between your two versions.

I have tried this and get an error message 'The remaining text does not appear to be part of the formula'; the cursor jumps to line 2, between length{subset.domain}

Penny

 

[IanWaterman]

Penny

Looks like the tektip viewer will not show the square brackets, these are the lower set of brackets adjacent to the letter P on the key board. The variable i should be enclosed in these brackets. That is why your formula is jumping, the variable i steps through the field name one letter at a time. so in long hand the line with the error should be

(If {Fieldname}( in square brackets insert i, no need for these parentheses) = "." then Fullstop :=1 else 0) else
If fullstop = 1 then

Sorry for the confusion, if you are still having trouble send your formula to ian_waterman@hotmail.com and I will take a look at it.

Ian

 

[Naith]

Ian,

[i]

is the code for italics. To display your

[i]

, surround your code in

[code]

, or use a different variable name.

Naith

 

[lbass]

Another approach would be:

mid({yourstring}, instr({yourstring},".&quot+1)

-LB