Conditional Formula Look Up

Question

Hi

I am a real newcomer so excuse the dumb question - I searched on the community but couldn't find an answer ...

I have a column of data some of which is a mix of customer names / 3 letter acronyms ... I would like to write a conditional formula based on the length of cells in that column that says "if the cell contains 3 letters then look up the 3 letter acronym in a lookup type table and replace it with the full name" - the end result being no acronyms are left in the original data ... I guess I need several formulas to achieve that ?

Thanks for the help !

Chris

PeterA1 · Accepted Answer

Hey @ChrisMcN I think a good approach would be to:

Pull on a filter tool and type "Length([Field])=3"

This will separate your column into length=3 and does not....

Now pull a Find Replace tool onto canvas and attach the "T" output of filter to the "F" anchor of Find Replace and attach the lookup table to the "R" anchor.

Find within Field will be the 3 letter acronym to be replaced by the value found in the lookup table...

then you can use a union tool to bring the "F" anchor of the Filter tool back with the Replaced data in the find/replace tool.

*attached a sample workflow

Let me know if any questions!

danilang · Accepted Answer

Hi @ChrisMcN

Yet another way

Since you have the lookup table, you don't actually need a check for the length of the input.  Just join all of them and the use a union to put all the records back together.

Input Becomes  
Output

Dan

ChrisMcN · Accepted Answer

Thanks so much to you (and everyone on the board !) - that worked perfectly

Chris