How to remove a specified number of characters from a string

A question was recently posted on the excellent Business Objects forum about how to remove a variable number of characters from the front of a string up to a specified character.

In this example the data was CKI=ICD9!250.02 and the user required all the values after the ! returned, i.e. 250.02. They also went on to state that the number of characters after the 250.02 may vary in length.

Interesting. What immediately sprang to mind was the MID() function available in Excel. However, this is not available in Web Intelligence.

However, Web Intelligence does provide us with the Right(), Length() and Pos() functions. Used together in a formula, we can get the desired result.

The first step is to determine the length of the character string. We are told it could be a variable length, so lets determine its length:

=Length([Object])

In this case we get 15.

Next we determine the position of the exclamation mark using Pos():

=Pos([Object];”!”)

This gives us 9

Finally, we use the Right() function. This takes two values; an input string and the number of characters to retrieve from the right of the string.

The second part is built up using the Length and the Pos calculations we made earlier.

=(Length([Object])-Pos([Object];”!”))

This gives us a value of 6. We should therefore count six characters from the left of the character string.

Putting this all together we create this formula:

=Right([Object];(Length([Object])-Pos([Object];”!”)))

…giving us the desired result of 250.02

%d bloggers like this: