1

I am currently using the following code :

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," ")

to achieve the result : text, text, text

However, I am struggling to change the code to get the result that if there's only 2 cells have data, it should be text & text. If 3 cells have data, it should be text, text & text.

can anyone help me on this?

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
ChingLu Tay
  • 49
  • 6

4 Answers4

0

Have you tried using CONCATENATE in place of the &""&?

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(TRIM(SUBSTITUTE(C28," ","^"))," ",TRIM(SUBSTITUTE(C29," ","^"))," ",TRIM(SUBSTITUTE(C30," ","^")))," ",", "),"^"," ")
Jesse
  • 1,814
  • 1
  • 21
  • 25
0

=IFERROR(REPLACE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "),MAX(IFERROR(FIND(",",SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "))))),0)),1," &"),SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "))

Bit of a mouth full but this'll do it... This is an array formula, confirm the formula with Ctrl+Shift+Enter while still in the formula bar.

Source

Glitch_Doctor
  • 2,994
  • 3
  • 16
  • 30
  • Thanks Glitch_Doctor for the solution! It works brilliant! – ChingLu Tay Jun 15 '18 at 14:36
  • Hi @Glitch_Doctor I have another problem which I couldn't solve with your formula now. I need to combine 2 cells in each substitute, for example, i include second cell to C28&""&C30 which resulted in text(text). I wanted to have text (text) with a space in between. C28&""&C30 is working fine with all cells but when i make a space in between C28&" "&C30, all go haywire. For example : xxx (xxx) ,& xxx (xxx) or xxx (xxx) & xxx (xxx), xxx (xxx). – ChingLu Tay Jun 11 '19 at 01:56
  • Hi @ChingLuTay, this is not how stack overflow works. As the scope has now changed a new question should be asked - I will take a look but could you post this as a new question linking this question or answer and I will respond in there? Thanks – Glitch_Doctor Jun 11 '19 at 11:00
0

Try this in xl2016/Office 365,

=textjoin(", ", true, c28:c30)

If you don't have the TEXTJOIN function, a good replacement UDF can be found at TEXTJOIN for xl2013 with criteria.

0

As a long-winded method that works across most versions of Excel, try this:

=IF(COUNTA(C28:C30)=3,TRIM(C28) & ", " & TRIM(C29) & ", " & TRIM(C29), IF(COUNTA(C28:C30)<2, TRIM(C28 & C29 & C30), IF(COUNTA(C28:C29)=2,TRIM(C28) & " & " & TRIM(C29), IF(COUNTA(C29:C30)=2,TRIM(C28) & " & " & TRIM(C29),TRIM(C28) & " & " & TRIM(C29)))))

How it works:

=IF(COUNTA(C28:C30)=3,TRIM(C28) & ", " & TRIM(C29) & ", " & TRIM(C29)
If we have 3 items, join them together with ", " between them

 

IF(COUNTA(C28:C30)<2, TRIM(C28 & C29 & C30)
For 0 or 1 items, join everything together to show either nothing or the single item

 

IF(COUNTA(C28:C29)=2,TRIM(C28) & " & " & TRIM(C29)
If we have just the first and second items, join them together with " & " between items

 

IF(COUNTA(C29:C30)=2,TRIM(C28) & " & " & TRIM(C29),TRIM(C28) & " & " & TRIM(C29))
If we have just the second and third items, join them together with " & " in between
Otherwise, join the first and third items, with " & " in between

 

)))
Close all of our IFs

 

Simplified & more versatile Excel-365 only version:

=IF(COUNTA(C28:C30)=2, TEXTJOIN(" & ", true, c28:c30), TEXTJOIN(", ", true, c28:c30))

(If there are only 2 items, skip blanks and join them with " & ", otherwise skip blanks and join them with ", ". This also handles lists of any length, not just 3 items)

Chronocidal
  • 6,827
  • 1
  • 12
  • 26