Skip to main content

I have a series of fields I want to concatenate together. The final result needs to include a semi-colon in between each piece of data, but not all columns have data. So I was working to do an IF statement to check if the last of the columns (far right column of data I want) had data, if so, concatenate it with the other fields. 

The formula I came up with (but is not a valid formula and I can't figure out why) is: 

 

 

IF({Revenue Rolodex}, CONCATENATE({Revenue Rolodex},”;”,{VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({VIP Day Social}, CONCATENATE({VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({4M Event}, CONCATENATE({4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({Applied to DID}, CONCATENATE({Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({DID Calculator}, CONCATENATE({DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({VIP Day Bootcamp}, CONCATENATE({VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({2022 Advent Calendar}, CONCATENATE({2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({VIP Day Quiz}, CONCATENATE({VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”),
IF({FREEBIE: Roadmap}, , )))))))))

 

 

 

However, I also now realize that even that doesn't take into consideration if another field after the first IF field is also blank for when it goes to concatenate. 

My brain is in overload mode thinking about how to make it leave out the blank fields, and NOT include additional semi-colons. I have 19K plus rows that I'm working to do this with so we can import it into a CRM, so going through and clearing out the duplicate semi-colons where not necessary would be nigh impossible. 

Any help would be greatly appreciated! Thanks in advance. 

100% sure this isn't the "correct" way to do this, but here's what I do - for every column that I need to concat, I make another column called Column; and IF(Column, CONCATENATE(Column, ";"), "") and then I concatenate the semi-colon columns together. There are more elegant ways, I’m sure.

Not sure if the coding block is messing up your formula above, but it's missing a bunch of commas - maybe that's why it's not working?

@pressGO_design 
Oh my, you're so right on the commas. I just put in a bunch around all the semi-colon additions, but I have a feeling I'm still missing some (or something else) as it still isn't valid hahah. 

I feel like there is probably a REGEX formula here, but I'm still trying to wrap my brain around those. 

Solution found!  Thanks to this Reddit thread which introduced me to the AI tool "Excel Formulator" I was able to put a prompt in that gave me this formula which works like a dream! Didn't even need the concatenate function. 

 

REGEX_REPLACE(IF({Revenue Rolodex}, {Revenue Rolodex} & ";", "") & IF({VIP Day Social}, {VIP Day Social} & ";", "") & IF({4M Event}, {4M Event} & ";", "") & IF({Applied to DID}, {Applied to DID} & ";", "") & IF({DID Calculator}, {DID Calculator} & ";", "") & IF({VIP Day Bootcamp}, {VIP Day Bootcamp} & ";", "") & IF({2022 Advent Calendar}, {2022 Advent Calendar} & ";", "") & IF({VIP Day Quiz}, {VIP Day Quiz} & ";", "") & IF({FREEBIE: Roadmap}, {FREEBIE: Roadmap}, ""), ";$", "")

 

 

Solution found!  Thanks to this Reddit thread which introduced me to the AI tool "Excel Formulator" I was able to put a prompt in that gave me this formula which works like a dream! Didn't even need the concatenate function. 

 

REGEX_REPLACE(IF({Revenue Rolodex}, {Revenue Rolodex} & ";", "") & IF({VIP Day Social}, {VIP Day Social} & ";", "") & IF({4M Event}, {4M Event} & ";", "") & IF({Applied to DID}, {Applied to DID} & ";", "") & IF({DID Calculator}, {DID Calculator} & ";", "") & IF({VIP Day Bootcamp}, {VIP Day Bootcamp} & ";", "") & IF({2022 Advent Calendar}, {2022 Advent Calendar} & ";", "") & IF({VIP Day Quiz}, {VIP Day Quiz} & ";", "") & IF({FREEBIE: Roadmap}, {FREEBIE: Roadmap}, ""), ";$", "")

 

 


Gorgeous! And another slap from the universe reminding me that I really need to get on the REGEX train. Thanks!

Reply


Terms & Conditions