Forum Discussion

Modnar1's avatar
Modnar1
Copper Contributor
Nov 18, 2022
Solved

Need help with Textjoin or concat function

So I have a row of fields and most will contain a number that to me has a value assigned to it. The value is not important and will be manipulated elsewhere. What I need is a summary of those values (essentially creating a code that acts as a roadmap. So I may enter a number in 5 fields but leave 3 empty because they do not apply, but still need it to fill into the overall "summary" value for the empty field.

 

Code 1Code 2Code 4Code 5Code 6Code 7SizeCode 8Code 9Code Summary 
1     2XL  1.2XLI need every field accounted for 
1     3XL  1......3XL..This doesn’t work either 
1     3XL  1.0.0.0.0.0.3XL.0.0This is what I need it to come out like 
1     Small    
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Nov 18, 2022

    Modnar1 

    =TEXTJOIN(".",,IF(ISBLANK(A2:I2),TEXT(A2:I2,"0"),A2:I2))

    You can try this formula which returns the intended result in my sheet.

2 Replies

  • Modnar1's avatar
    Modnar1
    Copper Contributor
    Nov 18, 2022
    Thanks that works, I knew that some easy way existed...
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 18, 2022

    Modnar1 

    =TEXTJOIN(".",,IF(ISBLANK(A2:I2),TEXT(A2:I2,"0"),A2:I2))

    You can try this formula which returns the intended result in my sheet.

Resources