Skip to main content
Solved

Simple formula returns field with additional brackets " "

  • February 25, 2022
  • 3 replies
  • 59 views
K
Forum|alt.badge.img+6

I wrote a very simple formula for my first field column to include fields from other columns in my table.
I have only two fields in my formula with text strings to include spaces between them. My issue is that one of the field ends up between two brackets like this " ".

As you can see, the field Prix Fab Gastier is = 12" BW STD in the table, but in my formula it returns “12"” BW STD"

So why is there in total 3 additional " ?
One before the 12
One after the 12"
and one after the STD

Best answer by Andy_Lin1

This is a common question – the reason why those extra quotation marks are there is because CSV (comma-separated value) parsers rely on quotation marks to group together items that have commas (or tabs/line breaks) within them, and any actual quotation marks need to be escaped (hence the duplicate quotation mark) and then that needs to get wrapped in quotation marks to indicate that that value needs special handling – and thankfully there have been some good solutions.

In addition to the straightforward solution by @augmented above*, there’s a slightly complicated solution that removes only the extra quotation marks. I’ve adapted @kuovonne’s excellent answer below:

Replace

& {Prix Fab Gastier}

with

& IF(LEFT({Prix Fab Gastier}, 1) = '"',
  SUBSTITUTE(
    MID({Prix Fab Gastier}, 2, LEN({Prix Fab Gastier}) - 2),
    '""', 
    '"'
  ),
  {Prix Fab Gastier}
)

*In a situation where the formula result is used in a lot of places, this can be preferable since you won’t have to deal with extra quotation marks in those other fields, even though you would lose the original quotation mark.

EDIT: Added missing open curly brace, as noted below

3 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • February 25, 2022

Hi Kevin. This happens when you have double quotes in a linked field you are concatenating with others. I haven’t tried it, but you could substitute out the " in {Prix Fab Gastier} in your Name formula with an empty string, and see what happens.

& SUBSTITUTE({Prix Fab Gastier}, '"', '')

Hope it helps.

K
A
Forum|alt.badge.img+17
  • Andy_Lin1
  • Inspiring
  • Answer
  • February 25, 2022

This is a common question – the reason why those extra quotation marks are there is because CSV (comma-separated value) parsers rely on quotation marks to group together items that have commas (or tabs/line breaks) within them, and any actual quotation marks need to be escaped (hence the duplicate quotation mark) and then that needs to get wrapped in quotation marks to indicate that that value needs special handling – and thankfully there have been some good solutions.

In addition to the straightforward solution by @augmented above*, there’s a slightly complicated solution that removes only the extra quotation marks. I’ve adapted @kuovonne’s excellent answer below:

Replace

& {Prix Fab Gastier}

with

& IF(LEFT({Prix Fab Gastier}, 1) = '"',
  SUBSTITUTE(
    MID({Prix Fab Gastier}, 2, LEN({Prix Fab Gastier}) - 2),
    '""', 
    '"'
  ),
  {Prix Fab Gastier}
)

*In a situation where the formula result is used in a lot of places, this can be preferable since you won’t have to deal with extra quotation marks in those other fields, even though you would lose the original quotation mark.

EDIT: Added missing open curly brace, as noted below

T
K
Forum|alt.badge.img+6
  • Kevin_Moreau
  • Author
  • Known Participant
  • February 28, 2022

This is a common question – the reason why those extra quotation marks are there is because CSV (comma-separated value) parsers rely on quotation marks to group together items that have commas (or tabs/line breaks) within them, and any actual quotation marks need to be escaped (hence the duplicate quotation mark) and then that needs to get wrapped in quotation marks to indicate that that value needs special handling – and thankfully there have been some good solutions.

In addition to the straightforward solution by @augmented above*, there’s a slightly complicated solution that removes only the extra quotation marks. I’ve adapted @kuovonne’s excellent answer below:

Replace

& {Prix Fab Gastier}

with

& IF(LEFT({Prix Fab Gastier}, 1) = '"',
  SUBSTITUTE(
    MID({Prix Fab Gastier}, 2, LEN({Prix Fab Gastier}) - 2),
    '""', 
    '"'
  ),
  {Prix Fab Gastier}
)

*In a situation where the formula result is used in a lot of places, this can be preferable since you won’t have to deal with extra quotation marks in those other fields, even though you would lose the original quotation mark.

EDIT: Added missing open curly brace, as noted below


Thanks to both of you, this works like a charm (was just missing a curly bracket for the first {Prix Fab Gastier} in the “LEFT” formula.

Terms & ConditionsAccessibility statement