CONCATENATE() different column values with a dinamic separator

[Gianlucapozza]

Hello, Im trying to concatenate 6 values into a virutal column that works as a summarization column in a table view and I need those values separated by a separator of some kind.  Ive used as separator " " since I wanted the space but, if the values of the columns concatenated are empty then the virtual column is not blank but made of "     " (5 spaces). Is there a way to concatenate those values without having to force a separator?

[Gianlucapozza]

Tried this workaround:

IF(ISBLANK([Invio dati]),"",[Invio dati]) &

IF(ISBLANK([Pulizia canali]),""," "&[Pulizia canali])&

IF(ISBLANK([Canali]),""," "&[Canali])&

IF(ISBLANK([Batteria]),""," "&[Batteria])&

IF(ISBLANK([Minimo notturno]),""," "&[Minimo notturno])&

IF(ISBLANK([Idrovalvola]),""," "&[Idrovalvola])&

IF(ISBLANK([Note]),""," "&[Note])

This way If something is to be concatenated the separator is coming from the IF() espression and not from the concatenation itself. If theres nothing to be concatenated nothing would be added so no invisible spaces that might make the virtual column NOTBLANK().

Now, how could I make it better looking and a bit more refined? I was thinking about using List() and SPLIT() but Ive never used those expressions.

[Steve · Accepted solution]

Try this:

SUBSTITUTE(
  CONCATENATE(
    LIST(
      [Invio dati],
      [Pulizia canali],
      [Canali],
      [Batteria],
      [Minimo notturno],
      [Idrovalvola],
      [Note]
    )
    - LIST("     ", "")
  ),
  " , ",
  " "
)

[Gianlucapozza]

Need to try that, and also need to read about Substitute() and how List() works

[Steve]

SUBSTITUTE() - AppSheet Help

LIST() - AppSheet Help

Subtract values from a list - AppSheet Help

[Gianlucapozza]

Tried this and It works. I still dont get how that subtraction works:

LIST("     ", "")

and also what this part does:

 " , ",
  " "

 after that works. Need to study more I guess.

Thanks for the help!

[Steve]

1. LIST([Invio dati], ..., [Note]) creates a list containing the desired individual column values.
   
   
2. (... - LIST(" ", "")) removes from the list from (1) entries that consist solely of five consecutive spaces ("     "), or are completely empty (""). List subtraction also has the side-effect of removing duplicate values from the list.
   
   
3. CONCATENATE(...) converts the list from (2) to a Text value where each list item is separated from each other by a space-comma-space sequence.
   
   
4. SUBSTITUTE(..., " , ", " ") replaces the space-comma-space (" , ") sequence that separates each list item in the Text value from (3) with just a single space (" ") to give the desired list formatting.

[Gianlucapozza]

Thank you! Grazie

[franc]

Ciao @Gianlucapozza 

Could you please share the formula you are using and a screenshot of the virtual column configuration?

[franc]

Ok scusa

you have done that while i was writing

[Gianlucapozza]

IF(ISBLANK([Invio dati]),"",[Invio dati]) & IF(ISBLANK([Pulizia canali]),""," "&[Pulizia canali])&IF(ISBLANK([Canali]),""," "&[Canali])&IF(ISBLANK([Batteria]),""," "&[Batteria])&IF(ISBLANK([Minimo notturno]),""," "&[Minimo notturno])&IF(ISBLANK([Idrovalvola]),""," "&[Idrovalvola])&IF(ISBLANK([Note]),""," "&[Note])

[franc]

First suggestion, if you want to concatenate you should have a look at 

CONCATENATE()

Have you tried a formula by using this expression?

[Gianlucapozza]

Yes, it was my first try, problemi is I dont want the separator to be imposed by the CONCATENATE() expression because If I happen to have blank values to be concatenated the separator is still showed. Could use concatenate() instead of & in formula though.

[franc]

it shows the separators according to what your formula says... Share your previous formula if you like, and some examples of texts that show the problem of these separators

[dbaum]

Concatenate values with a delimiter: How to simulate the TEXTJOIN spreadsheet function - Google Clou...