Find a string that is similar and replace it

[backrocket]

Hello everyone,

I have some data which has a column full of phases. Although the phases are not uniform for example a phase called 2.0 Fieldwork, in this column can be fieldwork, FIELDWORK, 2.0-Fieldwork, fiedwork. All of them have the letters ' fie' in common among them. What I would like to do is to find this particular phase by only using the letters 'fie' and then on a new column have it show as 2.0 Fieldwork. 

The goal is to clean the data, I have multiple phases in this column all of which have certain letters common among them and I would like to use the formula tool to clean and replace it on a new column for me. 

In excel I have it setup as 

=IF(ISNUMBER(SEARCH("fie",K3)),"2.2 Fieldwork",.........

Thank you.

[gnans19 · Accepted solution]

Use formula tool and create a condition

IF Contains([Text],"fie") THEN "2.2 Fieldwork" ELSE Null() ENDIF

Attached sample workflow

[backrocket]

Thank you for the reply, but also as a follow up, can I do this as a nested IF statement?

[StuartFieldhouse]

Hi Ragupta,

It's almost the same as in Excel.  Put a Formula Tool in and set it to create a new string field.  Set the calculation of the new field to:

IF CONTAINS([K3],"fie") THEN "Fieldwork 2.0" ELSE "" ENDIF

(this assumes that the name of the field you are checking is "K3" - just put the real field name between the square brackets.)

HTH 

Stuart

[StuartFieldhouse]

Sorry gnans19 - you answered while I was typing!  But you got there first :-)

[gnans19]

No issues :)   Hi5!

[backrocket]

Also as a follow up, can I do this as a nested IF statement? because my actually if statement for excel is very big and is bellow: 

=IF(ISNUMBER(SEARCH("fie",K3)),"2.2 Fieldwork",

IF(ISNUMBER(SEARCH("qualita",K3)),"2.3 Qualitative",

IF(ISNUMBER(SEARCH("quantita",K3)),"2.4 Quantitative",

IF(ISNUMBER(SEARCH("qual",K3)),"2.5 Quality Control",

IF(ISNUMBER(SEARCH("del",K3)),"2.6 Deliverale",

IF(ISNUMBER(SEARCH("trav",K3)),"Project Travel Time",

IF(ISNUMBER(SEARCH("Res",K3)), "2.0 Research Phase",

IF(ISNUMBER(SEARCH("mana",K3)), "2.1 Project Management",

IF(ISNUMBER(SEARCH("out",K3)),"0.0 Out of Scope",

 IF(ISNUMBER(SEARCH("eng",K3)),"Eng. Planning, Setup & Administration","Delete"))))))))))

[gnans19]

Nested if is possible

IF Contains([Text],"fie") THEN "2.2 Fieldwork"
ELSEIF Contains([Text],"not") THEN "2.3 NONE"

.

.

.

.
ELSE Null() ENDIF

[backrocket]

Alright perfect thanks!