Search and replace of multiple values.

Alfredo Monasi
Alfredo Monasi Experian Employee
edited September 30 in General

Hi everyone!,

I have a table with 2 columns, the first one is the string I should check inside a field, the other column is the value I should replace if its a match. How can I achieve this without creating a function with lots of steps for each string I need to search and replace.

ORIGINAL STRING

NEW STRINGTEXTO NUEVO

-|

|

FIS|

FISICA|

LITERAT|

LITERATURA|

,|

|

ABODADO

ABOGADO

ABODAGA

ABOGADO

ABOGADA

ABOGADO

ABOGADA

ABOGADO

ABOGADO-

ABOGADO

ABOGADO (A)

ABOGADO

ABOGADO(A)

ABOGADO

ABOGADOA)

ABOGADO

AGOGADO

ABOGADO

BACHILLER EN ADMINISTRACION HOTELERA Y DE TURISMO ADMINISTRACION HOTELERA Y DE TURISMO

BACHILLER EN ADMINISTRACION HOTELERA Y DE TURISMO

BACHILLER EN ADMINISTRACION TURISTICO -

BACHILLER EN ADMINISTRACION TURISTICO

BACHILLER EN CIENCIAS ADMINISTRATIVAS|

BACHILLER EN ADMINISTRACION|

BACHILLER EN CIENCIAS CONTABLES Y FINANCIERAS|

BACHILLER EN CONTABILIDAD|

BACHILLER EN CIENCIAS DE INDUSTRIAS ALIMENTARIAS"

BACHILLER EN CIENCIAS DE INDUSTRIA ALIMENTARIA

BACHILLER EN CIENCIAS INDUSTRIAS ALIMENTARIAS

BACHILLER EN CIENCIAS DE INDUSTRIAS ALIMENTARIAS

BACHILLER EN CIENCIAS INGENIERIA

BACHILLER EN CIENCIAS DE LA INGENIERIA

BACHILLER EN CIENCIAS SOCIAL

BACHILLER EN CIENCIA SOCIAL

BACHILLER EN CIENCIAS SOCIALES:

BACHILLER EN CIENCIA SOCIAL

BACHILLER EN CIENCIAS: FORESTALES

BACHILLER EN CIENCIAS FORESTALES

Cheers,

Alfredo

Answers

  • James T Sidebotham
    James T Sidebotham Contributor

    Will these be the only values within the field, or are you looking inside say a comments filed? If it's the first one, I would just do a simple lookup using the table you have here as a lookup table. At least that way you stay flexible and can add more values later.

    If it's the second scenario (within comments type field), then I'm not sure top of my head. Maybe some expression replacement?

  • Alfredo Monasi
    Alfredo Monasi Experian Employee

    Hi James,

    In this case I need to look inside a whole string for the specific pattern and replace it.

    Example:

    I have a field, for example ABCD1EFG. Then I have a table with 2 columns ToFind, To Replace

     

    ToFind

    ToReplace

    1

    X

    2

    Y

    so in the example would replace to ABCDXEFG

     If its ABC1DEF2GH should be replace as ABCXDEFYGH

     Cheers,

    Alfredo

  • James T Sidebotham
    James T Sidebotham Contributor
    edited September 30

    Still use the lookup table, but use the function "Replace Matches"? I tried and seems to work within a comments field (space separated values before and after) with your original strings

  • James T Sidebotham
    James T Sidebotham Contributor
    edited October 20
    image.png
  • Alfredo Monasi
    Alfredo Monasi Experian Employee

    Thanks, it worked when is a whole word to replace with spaces. For other case inside a string I had to use a replace.

Categories