Skip to main content
Solved

Concatenate Problem

  • December 2, 2022
  • 11 replies
  • 75 views
S
Forum|alt.badge.img+16

Hi everyone! I hope you had an amazing Thanksgiving!

I have some problems with my formula, I'm trying to concatenate two columns (see attachment ). As you can see, the Calculation column missed the "commas" of the originals columns, and moreover, if there is anything in a column, it adds a comma.

I would like that my calculation is :

1 Report Name2- Report NameCalculation
D, EC, FD, E, C, F
F, CF, C
D, ED, E

my formula is in the attachment.

What am I doing wrong?

Thank you very much!

Best answer by TheTimeSavingCo

Base I created a "fake" base, so you can try it if you want. Thank you very much!


Hi Sara! Thank you very much for creating the base for me!



I added kuovonne's original solution about the `ARRAYJOIN()` to the formula and I think it should be what you're looking for

```

IF(
AND(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
),
ARRAYJOIN({1- Report Name}) & "," & ARRAYJOIN({2- Report Name}),
IF(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
)
)
```
1 Attachments

11 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • December 2, 2022

Your first two columns are lookup fields. That means that the commas you see in the lookup fields themselves aren't really there. Lookup fields are the most problematic fields to use in formulas. Try turning your lookup fields into rollup fields with the formula

ARRAYJOIN(values, ", ")
S
S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 2, 2022

Thank you very much for your reply! I really appreciated it!

Do you know how I can solve the problem of the blank cell? I would like that airtable won't add a comma when there is a blank cell.

TheTimeSavingCo
Forum|alt.badge.img+31

Thank you very much for your reply! I really appreciated it!

Do you know how I can solve the problem of the blank cell? I would like that airtable won't add a comma when there is a blank cell.


Hi Sara, try this:
```
IF(
{1- Report Name}
{1- Report Name) & ", " & {2- Report Name}
{2- Report Name}
)
```

Adam, Airtable Consultant - thetimesaving.company
S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 5, 2022

Hi Sara, try this:
```
IF(
{1- Report Name}
{1- Report Name) & ", " & {2- Report Name}
{2- Report Name}
)
```


Hi Adam, thank you for your help! do you mean with the CONCATENATE function?

TheTimeSavingCo
Forum|alt.badge.img+31

Hi Sara! So sorry, that is nowhere near the correct formula that I was supposed to give you. I was getting some errors trying to reply with the formula and must have somehow messed up sigh. I still can't do so now, but you can duplicate this base I've set up for you and grab the formula from there

Adam, Airtable Consultant - thetimesaving.company
S
S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 5, 2022

Hi Sara! So sorry, that is nowhere near the correct formula that I was supposed to give you. I was getting some errors trying to reply with the formula and must have somehow messed up sigh. I still can't do so now, but you can duplicate this base I've set up for you and grab the formula from there


Thank you! I really appreciated that you had the time to create a base for me. Your formula works if the column "Report names" is just a text. I tried to use your formula on rollup and on lookup (Unluckily my reports are in one of those formats) and it "eats" the comma between some letters.

I attached the screenshot

Did I do something wrong? 🤔 🤔

TheTimeSavingCo
Forum|alt.badge.img+31

Thank you! I really appreciated that you had the time to create a base for me. Your formula works if the column "Report names" is just a text. I tried to use your formula on rollup and on lookup (Unluckily my reports are in one of those formats) and it "eats" the comma between some letters.

I attached the screenshot

Did I do something wrong? 🤔 🤔


Hmm, I think the way I've got my base set up might be slightly different? The formula seems to work fine with lookup fields

 

Adam, Airtable Consultant - thetimesaving.company
S
S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 5, 2022

Hmm, I think the way I've got my base set up might be slightly different? The formula seems to work fine with lookup fields

 


Did I miss something in the formula? or did I wrong typing something?😨

S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 5, 2022

Hmm, I think the way I've got my base set up might be slightly different? The formula seems to work fine with lookup fields

 


Base I created a "fake" base, so you can try it if you want. Thank you very much!

TheTimeSavingCo
Forum|alt.badge.img+31

Base I created a "fake" base, so you can try it if you want. Thank you very much!


Hi Sara! Thank you very much for creating the base for me!



I added kuovonne's original solution about the `ARRAYJOIN()` to the formula and I think it should be what you're looking for

```

IF(
AND(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
),
ARRAYJOIN({1- Report Name}) & "," & ARRAYJOIN({2- Report Name}),
IF(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
)
)
```
Adam, Airtable Consultant - thetimesaving.company
S
S
Forum|alt.badge.img+16
  • Sara11
  • Author
  • Inspiring
  • December 6, 2022

Hi Sara! Thank you very much for creating the base for me!



I added kuovonne's original solution about the `ARRAYJOIN()` to the formula and I think it should be what you're looking for

```

IF(
AND(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
),
ARRAYJOIN({1- Report Name}) & "," & ARRAYJOIN({2- Report Name}),
IF(
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({1- Report Name}),
ARRAYJOIN({2- Report Name})
)
)
```

It works😍!! Thank you very much!! I really appreciated it!  😀

Terms & ConditionsAccessibility statement