Assignment/Validation-How Replace will work?

[Former Member]

Hi, I need to write formula which can replace value of the field.

For example: In Assigement, I need to check Name1 field for Company name and than replace the value with new value. There is no REPLACE function available in Assigement tab in data manager. How can I replace the value?

For example:

Name 1 contains Mr. Robert D'soza- For this value I need to remove Mr. and last name and wants to make name1: Robert and name2: D'soza. I tried different ways like trim left, trim right and all but unable to write excact formula.

Can some one help me out ???

Regards,

R.n

[Former Member]

Hi Ron ,

Though there is no explicit replace function you could use an assignment like this for your requirement:

if (isnotnull(namefield),trim(2/3)) - in case you want to remove the . after the mr also

and once that is removed you could write a similar function to remove the surname- the question now is if you different name to trim in this manner because then for the surname you would have to independantly calculate the number of characters to be trimmed.

Regards,

Anita

[Former Member]

Hi Anita, we have 3 function -- Trim Left, Trim Right and Trim ALL.

Even if I will use 2/3 in my formula, it use to give 0.666666...

Do we need to give ASCII value to work with charactar?

Let me tell you my requirement..

I have 100m records. I need to write generalized formula which will identify company name starts with The, A, etc. And remove The, A from company name.

For example: "The ABC Ltd." I need to find occurance of 'The' and remove them. But at the same time if some name only start with-- for example: "Themself Ltd" than it should not remove this 'The' from this name...

Let me know how is it possible to work on such requirement? To me it looks impossible with this tool to work on such requirement...

thx,

R.n

[former_member201266]

Hi R.n.

This is really simple; just you got to itterate it and know the small difference in the options, dont think its impossible, just use sample things and you will get it.

but for the scenario you said the solution is

If(LEFT(name,4)=”The “,RIGHT(Name,len(Name)-4,Name)

here Name is the field name which you want to edit.

In case of any other issues; please let me know

Regards,

CHARAN

[Former Member]

Hi Rn ,

Have you found a solution as yet ? Well, sorry for the delay, but the trim all function trims every thing else except what is mention in paranthesis.

For example: the follwoing statement

If(Is_not_null(Name1),Trim_all("The"),Name1)

removes all the other characters in the name field except ´the' . Though this is not entirely your requirement, you could use this explicitly on records to remove the 'the' by specifying the rest of the name in the trim statement.

Will keep trying to check if a more generic assignment can be made.

By the way you could use the value conversion filter in the import manager o nthe name 1 field to replace the ´The´characters with spaces , am sure you must considered this option too .

Do let us know how your issue is solved.

Regards ,

Anita