Skip to main content
Topics#Data ManagementKathy Carter's Question
Kathy Carter (J. B. Hunt Transportation) asked in #Data Management
Jul 25, 2014, 6:35 PM

How do I format the values in a comma delimited field so that each value is formatted to the same pattern using workflow?

I have a field that could possibly have more than one value separated by commas.  I need to make sure that all values begin with "ABC "  so that the resulting field value would look like ABC value1, ABC value2, ABC value3....  I was thinking it could be done with SUBSTITUE and REGEX, but I can't get it to work.  Something like SUBSTITUTE(fieldname, ", [^"ABC"], ", ABC") but I get Error: Syntax error. Missing ')'.  Any ideas?
5 answers
  1. Justin Smith (Mascoma Bank)
    Jul 25, 2014, 8:06 PM
    Ok, try this:

     

    Create 2 field updates for your workflow rule:

     

    1. IF(BEGINS(Field, 'ABC'), '', 'ABC ') &

     

    SUBSTITUTE(Field, ',', ', ABC')

     

    2. SUBSTITUTE(Field, 'ABC ABC', 'ABC')

     

    The first will replace all commas with comma and your text

     

    The second will edit remove any duplicates if the text is already there.

     

    The only thing to be careful of is SUBSTITUTE is case sensitive so it will not cover is if someone enters Abc or abc or AbC.
Write an answer...
0/9000