Concat Function

Copper Contributor

Hi I am trying to use formula to create product name. Here is my data:


BrandMaterial 1SeriesProductMaterial 2SizeShapeColor
NRS  Pure Vanilla Extract 100ml  
MP  Spiral Cutter    Green
MP SelectExtra Coarse Grater   Red
MP PremiumZester   Retro Green


I am currently using this formula: =CONCAT(B4," ",C4," ",D4," ",E4," ",F4," ",G4," ",H4," ",I4). If i use this formula, what comes up is: NRS  Pure Vanilla Extract 100ml   (too many spaces between words). I want to create a "proper" product name so if the column material is empty, it will just be skipped and not printed as space.


Will appreciate your kind assistance.


2 Replies


If you have Microsoft 365 or Office 2021, or use Excel in the browser or on iPhone/iPad/Android:


=TEXTJOIN(" ", TRUE, B4:I4) 

@Hans Vogelaar 


Thank you very much. It works like a charm.