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

Advertisements

9 Responses to “How to remove a specified number of characters from a string”

  1. Juan Gutierrez Says:

    Hi… I have a similar problem, is there any way to remove back to front .. example: category_symbol = “testcategory.I need to cut.the last point.last column” / must be “testcategory.I need to cut.the last point” how to do this?? Thank´ss!

    • weldblog Says:

      Juan

      I’m not sure I quite understand what you are trying to do.

      Do you have the following data:
      testcategory.I need to cut.the last point.last column

      And require it to be output as follows?
      testcategory.I need to cut.the last point

      If this is correct, then you want to delete every after the last point? Please confirm and I’ll see what I can work out.

      • Juan Gutierrez Says:

        Yes, it´s correct!
        I want to delete every adter the last point…

        Example: the line: “xxxxx.aaaaaa.bbbbb.zzz” will be: “xxxxx.aaaaaa.bbbbb”

        Thanks for help and hugs!

      • weldblog Says:

        Juan

        This one has me stumped! I can’t find a way of doing this in Web Intelligence or Business Objects full client.

        I suspect this is something that you will have to do at the database level.

        Do any of our readers have an answer?

  2. Juan Gutierrez Says:

    Hello,
    I found a way to solve this, but it is very annoying!

    To separate this string into 3 columns: “aaaa.eeee.uuuu”
    getting:
    Column A: aaaa
    Column B: eeee
    Column C: uuuu

    We need to create some variables in BOXI:

    @ Column A =Substr([Field Symbol];0;+Pos([Field Symbol];”.”))

    @ Check1: =Right([Field Symbol];(Length([Field Symbol])-Pos([Field Symbol];”.”)))
    @ Check2: =Substr([Check1];0;+Pos([Check1];”.”))

    @ Column B: =If(IsNull([check2])) Then(Right([Check1];(Length([Check1])-Pos([Check1];”.”))))
    Else(Substr([Check1];0;+Pos([Check1];”.”)))

    @ Check3: =Right([Check1];(Length([Check1])-Pos([Check1];”.”)))
    @ Check4: =Substr([Check3];0;+Pos([Check3];”.”))

    @ Column C: =If(IsNull([check4])) Then(Right([Check3];(Length([Check3])-Pos([Check3];”.”))))
    Else(Substr([Check3];0;+Pos([Check3];”.”)))

    Then we can remove the “.” is necessary to replace the one variable, for example:
    @ Column A-1: =Replace([Column A];”.”;””)

    Thank you,

    • weldblog Says:

      Seems like a lot of work to achieve something that should be straightforward, doesn’t it?

      Anyone else have any solutions?

    • Vamshi Says:

      Thanks for the post, I am trying to achieve something similar – I want to get the first name, last name from a “firstname.lastname@email.com” email string. thanks agiain..

  3. Genelle Says:

    Hey thanks for this! It was really helpful to me- I had to lop off 6 characters from a string and this worked well when I took out the Length portion of your formula. Mine turned out like this:
    Left(UserResponse(“prompt”);Pos(UserResponse(“prompt”);” (“))

    The prompt answers all ended in three characters enclosed in parentheses, so I wanted everything left of the ” (“.

    I have a blog as well- I’ll link to yours when I write about this.. 🙂 Thanks again!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: