URGENT: add quotation marks to fields that contain commas

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}
)

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!!!

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

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.

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.

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.

most of the Fav Char data is comma free

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.