How to seperate 3 columns from a Single column

[satyakumar]

Hi Folks,

I have a quick question in Crystal Reports.

From the data base one field data is coming like

12345 34 hhdghgdfh

In crystal reports i have to show above data in three different columns. We can divide the field only by the Space. So i have to show 12345 as column1 and 34 as column2 and hhdghgdfh as Column3.

So please help me in writing the formulae.


Thanks
Satya

 

[briangriffin]

split({YourField}," ")[1]

split({YourField}," ")[2]

split({YourField}," ")[3]

 

[satyakumar]

Hi Brian,

The first split is working but not the other two.

 

[briangriffin]

You're sure there's only one space between the values?

If so, are you sure there is just one space in the formulas for the second and third values?

 

[satyakumar]

No not really one space between them. But space is the key for us to divide entire field by three.

 

[briangriffin]

So there are two spaces between, three spaces, or does it vary by record?

 

[satyakumar]

Its vary by record.

 

[briangriffin]

If it's only 1,2, or 3 spaces then you could create a formula like this (I'll call it {@Replace}:

replace(replace(replace({YourField}," "," ")," "," ")," "," ")


Then the original formulas would work, for example

{@Replace}[1]

If the number of spaces vary greatly then a different approach would be necessary.

 

[briangriffin]

Here's the formula that accomodates any number of spaces - I'll call it {@Loop}:

stringvar x := "";
numbervar i := 1;

while i <= len({YourField})

do(
if ascw({YourField}) <> 32
or (ascw({YourField}) = 32 and ascw({YourField}[i + 1]) <> 32)
then
x := x + {YourField};
i := i + 1);

x


Then your formulas for display are:

split({@Loop}," ")[1]
split({@Loop}," ")[2]
split({@Loop}," ")[3]

 

[satyakumar]

Thanks Bro. Now everything is working good, But in one scenario its showing little wrong.

Ex: The field in the data base is : 002767320 1224 Brown, M

Now we have to seperate 002767320 as column1 and 1224 as column2 and Brown, M as column3.

I dont know why again they are giving space after the Brown, M

Above example i have shown is less number of people they are having records in the database like this, but 99% of the customers their names are showing only In one word not like Brown, M

Please help me dude in writing the formuale.

 

[briangriffin]

As long as you only have three values, and the last value is the one that potentially has the extra space, this should work:

mid({@Loop},instr({@Loop},{@Two} + " ") + len({@Two}) + 1)