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