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:

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:

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:

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:

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”)))))

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.