Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 06, 2021 09:28 AM
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.
Nov 06, 2021 11:36 AM
A little dated, but this might help -
Nov 06, 2021 11:56 AM
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}
)
Nov 06, 2021 12:32 PM
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!!!
Nov 06, 2021 01:00 PM
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
Nov 06, 2021 01:05 PM
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}
)
Nov 06, 2021 01:21 PM
You need to create a new formula field.
Nov 06, 2021 01:24 PM
I’ve changed the Favorite Characters from single line text to formula. Do u mean I need another field?
Nov 06, 2021 01:25 PM
Yes. Keep the existing single line text field. Add a new formula field that references the existing field.
Nov 06, 2021 01:28 PM
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.