Skip to main content
Solved

Concate but exclude grammar along with excluded cell

  • September 4, 2020
  • 2 replies
  • 37 views
M

Hi.

Noob alert!

This is my first attempt at formulas of this nature. I am trying to concate an address (Street 1, Street 2, City, State, Postcode, Country), separated by a comma and space (", ").

However, most addresses I enter do not fill the ‘Street 2’ cell, which results in an additional ", " in the formula outcome. I have spent hours trying to correct this and cannot figure it out.

I would be grateful for any assistance! Thank you.

Here is an image of the issue:

Shared with CloudApp

Here is the fomula I am using:

CONCATENATE({Street 1},", “,IF({Street 2},’’&{Street 2}),”, “,{City},”, “,{State},”, “,{Postcode},”, ",{Country})

Best answer by Megan_Cartwrigh

I figured it out :slightly_smiling_face:

IF({Street 2},CONCATENATE({Street 1},", “,{Street 2},”, “,City,”, “,State,”, “,Postcode,”, “,Country,”, “),CONCATENATE({Street 1},”, “,City,”, “,State,”, “,Postcode,”, ",Country))

2 replies

M
  • Megan_Cartwrigh
  • Author
  • New Participant
  • September 4, 2020

Trying to add a better picture of issue:

M
  • Megan_Cartwrigh
  • Author
  • New Participant
  • Answer
  • September 4, 2020

I figured it out :slightly_smiling_face:

IF({Street 2},CONCATENATE({Street 1},", “,{Street 2},”, “,City,”, “,State,”, “,Postcode,”, “,Country,”, “),CONCATENATE({Street 1},”, “,City,”, “,State,”, “,Postcode,”, ",Country))

Terms & ConditionsAccessibility statement