Extracting part of a string in Crystal Reports

A client had a Crystal Reports field that looked like:

She wanted to be able to extract the part of the text within brackets, i.e. jdonbavand11 or joe.bloggs.

There well be more than one way of achieving this but this is what I did:

I created one formula field which I called user1. The formula I entered was

right({tablename.User name},len({tablename.User name})-InStrRev ({tablename.User name},”(” ))

What this formula does is select the rightmost characters occurring after the opening bracket (. The len function looks at the length of the field. The InStrRev
function goes backwards from the end of the string to find the named character in the string. The result of this is

jdonbavand11)

joebloggs)

I then created another formula field user2.

left({@user1},len({@user1})-1). This takes all the characters of the string until the closing bracket and so removes the final ) from the output.

If all they had wanted was the part of the field to the left of the opening bracket, the formula would have been

Left({tablename.User name},InStrRev({tablename.User name},”(“)-1)

This counts to where the first ( is and returns all the characters to the left of the opening bracket, returning

Janet Donbavand

Joe Bloggs

To find out more about our Crystal Reports training Sydney, Australia, please visit http://www.jmdtraining.com.au/it-training/crystal-reports-training.

To find out more about our Crystal Reports training London, England, please visit http://www.jmdtraining.co.uk/it-training/crystal-reports-training.

About these ads

About Janet Donbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Crystal Reports, Uncategorized and tagged , , . Bookmark the permalink.

One Response to Extracting part of a string in Crystal Reports

  1. nnya says:

    useful blog, thanks

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