How to extract data from the same column when is variable

[jtammyg]

Hi there!!!

I need urgent help with this please.

I have the following data in the same column:

Port: X Jack: Type: 2500
Port: X Jack: Type: fax
Port: 01D1503 Jack: Type: K2500
Port: X Jack: Type: 8410D
Port: X Jack: Type: H.323
Port: 01D0804 Jack: Type: 8411D

I want to have 3 different columns in the report:

Port Jack Type
X 2500
etc

How do I go about creating the formula?

I cannot do a direct left oor right, since the length of the Port, Jack and Type can be variable.

Thanks a lot for your help!!!!!!

 

[synapsevampire]

Try:

formula1:
left("Port: X Jack: Type: 2500", instr("Port: X Jack: Type: 2500"," Jack&quot)
Formula2:
mid("Port: X Jack:012 Type: 2500",1+instr("Port: X Jack:012 Type: 2500"," Jack&quot,
instr("Port: X Jack:012 Type: 2500"," Type&quot-(1+instr("Port: X Jack:012 Type: 2500"," Jack&quot))
Formula 3
mid("Port: X Jack: Type: 2500",1+instr("Port: X Jack: Type: 2500"," Type&quot,100)

Replacing "Port: X Jack:012 Type: 2500"
with your field.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jtammyg]

synapsevampire, thanks!

The only formula that does not work is the formula 2.

The others work great!

Any idea why is giving me an error "string length is less than 0 or not an integer"?

Thanks in advanced!!!

 

[synapsevampire]

Hmmm, sounds like all of your fields do NOT contain the data format specified.

You may need IF THENS around each of these to generate a default if the INSTR() function returns 0 (meaning that the data isn't as you expected).

Try:

If instr("Port: X Jack:012 Type: 2500"," Jack&quot > 0 then
mid("Port: X Jack:012 Type: 2500",1+instr("Port: X Jack:012 Type: 2500"," Jack&quot,
instr("Port: X Jack:012 Type: 2500"," Type&quot-(1+instr("Port: X Jack:012 Type: 2500"," Jack&quot))
else
"Didn't Find either the text Jack OR Type"

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jtammyg]

Works like a charm

Thanks Kai!!!!

 

[ratneswar]

Is it possible to write this with basic syntex

 

[dgillz]

It is possible to write any formula in either crystal or basic syntax, this is a choice made at the time the formula is written.

Look in the upper rioght hand area of the formula editor windows and there is a drop down list box with 2 options, "Basic Syntax" and "Crystal Syntax". Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Also, keep in mind that Basic syntax isn't the same as VB or VBA.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com