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

Terms & ConditionsAccessibility statement