Replacing values in a string

I received a request for help in a comment on one of my blog posts.

I don’t normally offer a helpdesk service, but I found the problem both interesting and challenging, so I thought I’d give it a go.

The question was:

“Hope you could help me here. I want to sort out free format text object which contains keywords. For example,

aaa/bbb/blue
ccc/ddd/yellow
eee/fff/red
ggg/hhh/xyz

Find “blue” from the object and replace the entire object with “azul”, find “yellow” then replace with “amarillo”, “red” into “rojo”. When neither “blue”, ”yellow” nor ”red” was found, replace the object with “not found”. So in the above example, “ggg/hhh/xyz” will be displayed as “not found”.

Thanks in advance for your assistance.”

Not being familiar with the entire data set or possible range of values, I need to make a few assumptions.

I assume you want to see the following output:

aaa/bbb/azul
ccc/ddd/amarillo
eee/fff/rojo
ggg/hhh/not found

I also assume that the position of the second forward slash may change.

If my assumptions are correct, we can move forward. If not, let me know!

When faced with such a problem, I like to break it down into logical steps, building up the formula until I get the result I’m after.

Now I’m assuming that you

For those that want to follow, I created a sample data file in Excel:

Excel Input

I then created a report using the Excel file.

The challenge is to replace the text after the second forward slash with another value depending on its content.

So lets start.

Using the Pos() function, you can determine the position of the first forward slash:

Output1

This gives us 4.

We need to find the position of the second slash, so we create a new string using the output from the function Pos(<F1>,”/”). To do this we use substr().

Substr allows us to take a part of string. To use this we pass in the data value, its start position and the length of the string to return.

For our example, we will use the following formula:

SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10)

If you are using Web Intelligence, the following formula will work:

SubStr([F1];Pos([F1];”/”)+1;10)

With this formula we are saying:

Return a substring of <F1>, stating in position 5, and bring back 10 characters.

Notice I said position 5 (we added +1). The reason for this is that we want to bring back the string after the first forward slash.

We end up with the following results:

Output2

Now we have a new string to work with, we need to find out the position of the forward slash in this string. We use:

Pos(SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10) ,”/”)

If you are using Web Intelligence, the following formula will work:

Pos(Substr([F1];Pos([F1] ;”/”)+1;10);”/”)

If we put these two formulas together, our result should be everything after the second forward slash:

Output3
If you are using Web Intelligence, the following formula will work:

Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)

We now have a formula to determine the character string after the second forward slash.
We can now start work on the real solution. We want to check for three possible values. If none of these values are found, we return a fourth ‘catch all’ value.

To perform this check, we use an If() function together with the Else command.

We also make use of the Replace() function within the statement to swap out the relevant values.

So we end up with the following formula:

If(SubStr(SubStr(<F1> ,Pos(<F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10) ,”/”)+1 ,10)=”blue”) Then Replace(<F1> ,”blue” ,”azul”) Else
(If(SubStr(SubStr(<F1>,Pos( <F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,”/”)+1 ,10)=”yellow”) Then Replace(<F1> ,”yellow” ,”amarillo”) Else
(If(SubStr(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,”/”)+1 ,10)=”red”) Then Replace(<F1> ,”red” ,”rojo”) Else
“not found”))

If you are using Web Intelligence, the following formula will work:

If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”blue”; Replace([F1] ;”blue” ;”azul”); (If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”yellow”; Replace([F1] ;”yellow” ;”amarillo”); (If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”red”; Replace([F1] ;”red” ;”rojo”); “not found”)))))

Output4

Note: If you don’t want to see the whole string and simply want “azul”, “amarillo”, “rojo” or “not found” displayed, then change the Replace command slightly as follows:

Replace(<F1> ,<F1> ,”azul”) 

or in Web Intelligence:

Replace([F1];[F1];”azul”) 

So we have ended up with quite a long formula composed of several functions:

If()
Substr()
Pos()
Replace()

Remember:

We couldn’t just do a Replace(), as there are multiple values to replace.

We couldn’t use an If() and a Replace() function, as the data set and length is not known in advance. The position of the second forward slash may change.

Therefore we used the Pos() function to find the position of the second slash, and use the If, Substr and Replace functions to replace the contents following that second slash.

I hope you found this post helpful. If you have any comments, do let me know.

Advertisements

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: