postcode district extraction - PLEASE HELP

[MartUK]

I am using Crystal XI

I need to extract the postcode district of a postcode.
I am using a formula as follows :

left({FSLOCA.FSLOCA_POSTCODE},instr({FSLOCA.FSLOCA_POSTCODE}," ")-1)

I have various formats to extract, the above only works when there is a space eg XXX YYYY or XXXX YYYY or XX YYY etc
however on occasion There wont be a 2nd part to a code eg Bristol city centre is just BS1 and so the formula must ALSO take this into account and return BS1 , at the moment it flicks back to the formula as you scroll through the report. I need something which takes all possible postcode types into consideration. This is driving me crazy

 

[IanWaterman]

The easiest way to do this is use a Do While loop

Replace the post code field with yours and this should work fine.

global stringvar postcode:="";
local numbervar n:=1;

while {CLIENT.CLI_POST_POSTCODE}[n] <> " " do
(
postcode:= postcode + {CLIENT.CLI_POST_POSTCODE}[n];
n:=n+1;
);

postcode;

 

[IanWaterman]

Sorry missed the bit about the field only beeing the first part of the post code. Try amending formula as follows

global stringvar postcode:="";
local numbervar n:=1;

do
(
postcode:= postcode + {CLIENT.CLI_POST_POSTCODE}[n];
n:=n+1

while {CLIENT.CLI_POST_POSTCODE}[n] <> " " or n = length({CLIENT.CLI_POST_POSTCODE});

trim(postcode);

 

[MartUK]

Thankyou, you are a star it worked first time. I have been concentrating cleaning up the absolute rubbish data I been given also eg postcode staring with numbers, blanks and other odd things, plus nulls etc etc etc
Can you explain why my formula kept falling over as it scrolled throught the pages or when I was exporting to excel, just as matter of interest?

Thanks again!!!

 

[lbass]

Or you could use:

if instr({FSLOCA.FSLOCA_POSTCODE}," ") <> 0 then
left({FSLOCA.FSLOCA_POSTCODE},instr({FSLOCA.FSLOCA_POSTCODE}," ")-1) else
{FSLOCA.FSLOCA_POSTCODE}

-LB

 

[IanWaterman]

Your formula was failing beacuse the INSTR function was not finding a " " and then did not know what to do.

LBs formula will work just as well and is easier to use. I just like using variables and some times over do it. I also forgot about instr().

Ian