Clean up values

[Legacy 15162]

I had to link a file using a fixed width. It was the only viable solution at the time. Unfortunately this leaves some text that I need removed from my values. there are spaces and a | in the columns that I want to return. The field name currently is Plan Sales:[Field14]. How can I get rid of these spaces and | ? I need to just see the monetary value.

 

[bat17]

This is a bit messy but should work in A97 assuming that the pipe will only be at the end of the string (apart from spaces)

Sales: : IIf(InStr([Field 14],Chr(124))=True,Left(Trim([Field 14]),Len(Trim([Field 14]))-1),Trim([Field 14]))

Peter

 

[Legacy 15162]

I was able to find a method around my pipe broblem by using the left mid and right commands to pull the data without the problematic characters. thanks for the help!