Formula to change certain words in the detail field

[Former Member]

Hi All,

I want to know how to use a formula to identify certain words in the detail field, and change those words to something else. For example, the detail field, under the heading of toys, contains trains, airplanes, and cars. I want a formula that changes ever occurrence of "trains" to "bikes," and every "airplane" to "ships." How do I do that?

Secondly, some data have excessively long names that I want to truncate, for example, "ABC bank - XY branch." I need a formula that cuts off everything after the "-", how do I do that?

Thank You in advance!

Frank

[Former Member · Accepted solution]

Try using the function replace() to replace the text in the field like this

replace(,"trains","bikes") and for runcating the text use the function split() split(,"-")[1]

Hope this helps!

Raghavendra

[Former Member · Accepted solution]

Hi Raghavendra,

Thank You for your answers! They work, but I want to take the "replace" command and replace more than one key word in the data field. replace(,"trains","bikes") worked wonders for trains and bikes, but how do I include "airplanes", and "ships" in the same formula?

thank you!

Frank

[Former Member · Accepted solution]

replace(replace(,"trains","bikes"),'airplanes','ships');

and so on and so forth.

PS: Make sure that you take into account caps etc.

[Former Member]

I need additional help on this topic. Thank You.

[Former Member]

Can someone help me with this question? It seems like CR would be able to do this, but I'm unsure how to go about it.

Thank You in advance!

Frank

[Former Member]

Hi All again,

Along the line of this question, let's say I have a detail field that displays a date of when a product is sold. But instead of displaying the date sold, I just want to replace the date with a simple "SOLD." How do I do that?

I want the word "SOLD" to be displayed everytime the detail field displays a date.

Additionally, I'm awarding my sales staff with a $200 bonus for every sale, so how do I create a detail field that displays the $200 bonus every time the world "SOLD" is displayed? I'd also want to create a summation of all the bonus awarded as well.

Please help. Thanks,

Frank

Edited by: CRXI Newb on Jan 15, 2009 1:48 AM

[Former Member]

Hi Frank

As I understand you want to display the name as "SOLD" if it is date sold To do this mentioned below are the steps.

1) You have inserted the fiekld in the detail section.

2) Right Click the particular field> Select Format Editor> Click on Common tab> In the second pane click on "X-2" button infront of Display String.Here you need to write formula:

3) if <database field>= "date sold" then "SOLD" else "NOT SOLD".

SOLD is the static name given if condition satisfied.

Hope this will help you.

Regards

Asha.