Search String and return desired text 2

[williampzt]

Hi All

I have report with the field {Web.ClientIP} the current field looks like xxx.xxx.xxx.xxx. I would like to output only the first three octets ie: xxx.xxx.xxx. Any help would be much appreciated.

PS: I tried using the mid function, but due to the changing IP addresses it is not reliable. I have had good results in excel using the Search function to search out the 3 decimal, but I am not finding a similar function in Crystal.

Thanks
williampzt

 

[synapsevampire]

Try a formula containing:

left({Web.ClientIP}, 12)

-k

 

[williampzt]

Thanks… but unfortunately the IP address field varies, sometimes it may be xxx.xxx.xxx.xxx or xx.xxx.x.xxx, if I could find a way to search for the third decimal and output everything to the left I would be good to go.

 

[JoeCrystal]

Try something like this:

Dim ip As string

ip = "12.456.78.000"

Formula = Left(ip,Instr(ip,Split(ip,".&quot(4)) -2)

Depending on your data you might need to add some data check to avoid errors.

 

[mbarron]

Or in Crystal Syntax (instead of Basic):

stringvar ip:={your.ip.field};
left(ip,instrrev(ip,".&quot-1 );
Mike

 

[williampzt]

Thanks Joe... That seems to be working, however being a rookie I am not sure what you mean by add some data checks.

 

[mbarron]

Data checks would be, along the lines of, maiking sure that data exists in the field, or if the is a "." in the field.

To add the data checks, my formula would be:

if not(isnull({your.ip.field}) then

stringvar ip:={your.ip.field};

if instrrev(ip,&quot;.&quot<>0 then
left(ip,instrrev(ip,&quot;.&quot-1 )
Mike

 

[synapsevampire]

Here's a simpler means, I had taken you literally when you stated the format of the IP, I should have thought it through:

left({ip},instrrev({ip},&quot;.&quot-1)

-k

 

[williampzt]

Thanks to All! Formulas are working great!