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.
URGENT: add quotation marks to fields that contain commas
+17Would 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}
)
kuovonne wrote:
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!!!
kuovonne wrote:
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
kuovonne wrote:
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}
)
+17You need to create a new formula field.
kuovonne wrote:
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?
+17Landings_Holida wrote:
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.
kuovonne wrote:
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.
kuovonne wrote:
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
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.