How to strip a field for use in grouping? 1

[baybook]

Hi,

I'm trying to group data by a field in the database. The data contained is formatted with extra characters that I would like to strip out. For example:
Database value Formatted value
|12|Beef should be: Beef
|24|Chicken should be Chicken

I now that I can create a formula that will fix this for all known values(if value = |12|Beef then "Beef") but I would liek to know if there is a way to do this so that I can accomodate any unknown values. For example is it possible to create a formula that would ignore or strip everything before the second pipe?

Thanks in advance!

 

[vidru]

A little more sample data would be helpful. If the pattern is the same for every field - pipe, 2-digit number, pipe - then this formula would do it:

Right({Table.Field},4)

If some don't have the pipe, and/or the position of the second pipe can vary, then something like this might work for you:

StringVar Str := {Table.Field};

If InStr(Str, "|") <> 0 Then //check the existence of the pipe
// If it has one, find the position of the seconde one,
// and get the part of the string after it.
Right(Str, Len(Str) - (InStr( Instr(Str, "|") + 1, Str, "|")))
Else // Just display the string as is
Str;

-dave

 

[pbateman]

Create a formula where ** is your field name:

split(**,"|")[2];

Then use that formula to group on.

 

[baybook]

The second ones is working with some tweaking. Thank you!