Skip to main content

I have an urgent need to find data cells that contain commas and enclose the data with quotation marks

A single table, with identifiable columns where needed.

A little dated, but this might help -


Would a formula field for each column work?


IF(

    FIND(",", {field name}),

    '"' & {field name} & '"',

    {field name}

)

If some already have quote marks as expected, and some have quote marks that need escaping, you might need something a bit more complicated.


IF(

   AND(

        FIND(",", {field name}),

        OR(

            LEFT({field name}, 1) != '"',

            RIGHT({field name}, 1) != '"'

        )

    ),

    CONCATENATE(

        '"',

        SUBSTITUTE({field name}, '"', '""'),

        '"'

    ),

    {field name}

)

Would a formula field for each column work?


IF(

    FIND(",", {field name}),

    '"' & {field name} & '"',

    {field name}

)

If some already have quote marks as expected, and some have quote marks that need escaping, you might need something a bit more complicated.


IF(

   AND(

        FIND(",", {field name}),

        OR(

            LEFT({field name}, 1) != '"',

            RIGHT({field name}, 1) != '"'

        )

    ),

    CONCATENATE(

        '"',

        SUBSTITUTE({field name}, '"', '""'),

        '"'

    ),

    {field name}

)

HOORAY!!! First example should do it. Might I ask how it could be executed for all cells in a column? I only have about 8 columns and could do 'em one at a time.


Many thanks!!!

Would a formula field for each column work?


IF(

    FIND(",", {field name}),

    '"' & {field name} & '"',

    {field name}

)

If some already have quote marks as expected, and some have quote marks that need escaping, you might need something a bit more complicated.


IF(

   AND(

        FIND(",", {field name}),

        OR(

            LEFT({field name}, 1) != '"',

            RIGHT({field name}, 1) != '"'

        )

    ),

    CONCATENATE(

        '"',

        SUBSTITUTE({field name}, '"', '""'),

        '"'

    ),

    {field name}

)

I get a circular ref error with

IF( FIND(",", {Favorite Characters}), ‘"’ & {Favorite Characters} & ‘"’, {Favorite Characters} )


here’s the test data in the field Batman, Robin and Starwars

Would a formula field for each column work?


IF(

    FIND(",", {field name}),

    '"' & {field name} & '"',

    {field name}

)

If some already have quote marks as expected, and some have quote marks that need escaping, you might need something a bit more complicated.


IF(

   AND(

        FIND(",", {field name}),

        OR(

            LEFT({field name}, 1) != '"',

            RIGHT({field name}, 1) != '"'

        )

    ),

    CONCATENATE(

        '"',

        SUBSTITUTE({field name}, '"', '""'),

        '"'

    ),

    {field name}

)

Oops… same error with this one


IF(

AND(

FIND(",", {Favorite Characters}),

OR(

LEFT({Favorite Characters}, 1) != ‘"’,

RIGHT({Favorite Characters}, 1) != ‘"’

)

),

CONCATENATE(

‘"’,

SUBSTITUTE({Favorite Characters}, ‘"’, ‘""’),

‘"’

),

{Favorite Characters}

)

You need to create a new formula field.

You need to create a new formula field.


I’ve changed the Favorite Characters from single line text to formula. Do u mean I need another field?

I’ve changed the Favorite Characters from single line text to formula. Do u mean I need another field?



Yes. Keep the existing single line text field. Add a new formula field that references the existing field.


Yes. Keep the existing single line text field. Add a new formula field that references the existing field.


Hmm… since my current fields layout is critical for export CSV, I suppose I could add another field all the way at the end. But then I’d have to copy the " " text back to Favorite Characters field.


Yes. Keep the existing single line text field. Add a new formula field that references the existing field.


most of the Fav Char data is comma free

Reply


Terms & Conditions