Need help removing text from field

[Pio Soto]

Hi,

I'm using text in my delivery slip layout through a lookup field and want to remove text if "RO#" text is in the field

example: VS-321 The Severed Arm: Blu-Ray RO# 52975

i want to remove everything starting with "RO#" if the field contains it,

I know this can be done using LeftWords or RightWords but I can't get it done,

if anyone can help it would be much appreciated.

Thanks.

Pio

[comment]

I would suggest:

Let ( [
pos = Position ( text ; " RO#" ; 1 ; 1 ) 
] ;
If ( pos ; Left ( text ; pos - 1 ) ; text )
)

 

[Pio Soto]

Thank you, that works perfect when I use my data viewer to test it,

but how would I use it in my field if I already have a looked-up value selected

sorry I don't know how to do both I can either select calculation or looked-up in my options for field.

[comment]

You can incorporate a lookup in a calculation using the Lookup() function - say: 

Let ( [
text = Lookup ( Sourcetable::Sourcefield ) ;
pos = Position ( text ; " RO#" ; 1 ; 1 ) 
] ;
If ( pos ; Left ( text ; pos - 1 ) ; text )
)

This is assuming you need to have an independent copy of the looked up data, a copy that will not change if the lookup source field is modified. Otherwise you could simply use an unstored calculation field = 

Let ( [
pos = Position ( Sourcetable::Sourcefield ; " RO#" ; 1 ; 1 ) 
] ;
If ( pos ; Left ( Sourcetable::Sourcefield ; pos - 1 ) ; Sourcetable::Sourcefield )
)

 

[Pio Soto]

Thank you!! and thanks for taking the time to help. It works perfectly.

Pio

[Pio Soto]

Hi, 

What if I wanted to remove the"C" or any letter from my field

example of a field with a PO#: CG350C53

is there a way to remove the "C"s

thanks.

Pio

[comment]

Use the Substitute() function.