Find and Replace data

[dareinnyc]

Hi, I am looking to use the multi-formula tool (or any other tool) to find an replace any items starting with "U" within the data field [Grade Level] to a static "10".

In this instance, i have several rows with U10, U20, U21.. and i would like to replace all of them to "10". 

Thank you

[jrgo]

@dareinnyc 

I think a normal formula tools would work, if it's only against one field. Here are some options...

REPLACE([Grace Level], 'U', '')
TRIMLEFT([Grace Level], 'U')

[JoshuaGostick]

Hi @dareinnyc,

Would something like the formula below work for your needs?

IF StartsWith([Grade Level], "U")
THEN "10"
ELSE [Grade Level]
ENDIF

Thanks,

Josh

[dareinnyc]

Thanks! That worked! To take it one step further.. if I want to tag each combination to a different number, how do I do it? A multi-formula tool?

U20 tagged to 9

U21 tagged to 10

U22 tagged to 11

0000 tagged to 9

Note that the last one doesn't have a "U" in front of it.

[JoshuaGostick · Accepted solution]

If that is the case, and you only have to change these 4 values, then it's probably easier to simply create a longer IF statement.

So for example:

IF [Grade Level] = "U20" THEN 9

ELSEIF [Grade Level] = "U21" THEN 10

ELSEIF [Grade Level] = "U22" THEN 11

ELSEIF [Grade Level] = "0000" THEN 9

ELSE [Grade Level]

ENDIF

Then, you can just add to this if you don't have too many other values to change.

Josh

[Hadzramin_Rahman]

Hi @dareinnyc 

You can try to use Find Replace Tool

Workflow

Lookup_Replace_Value text input

Result