Concatenate/TextJoin using a different delimiter between 2nd Last and Last entry (Excel or Sheets)

[Jaypee666]

Hi all,
Not sure if what I'm looking to do is possible or not.
I have 2 columns M & N that populate based on another columns content.
I then have 2 cells that do a TextJoin on the relevant column, J15 does a TextJoin for Column M & J17 for N
What I'm wondering is, Is itpossible to Concatentate/TextJoin using a different delimiter between 2nd Last and Last entry in either Excel or Google Sheets.

So in the sample provided below is it possible to have J15 return Area 1, Area 2 & Area 3 and J7 to return Area 4 & Area 5

Board-Sample.xlsx
	J	K	L	M	N
13	NOTES			High	Moderate
14	HIGH
15	Area 1, Area 2, Area 3,			Area 1,
16	MODERATE
17	Area 4, Area 5,			Area 2,
18
19				Area 3,
20
21					Area 4,
22
23					Area 5,
24
25
Report-1

Cell Formulas
Range	Formula
M14,M16,M18,M20,M22,M24	M14	=IFNA(IF(G14="HIGH",CONCATENATE("Area ", A14,","), ""),"")
N14,N16,N18,N20,N22,N24	N14	=IFNA(IF(G14="MODERATE",CONCATENATE("Area ", A14,","), ""),"")
M15,M17,M19,M21,M23,M25	M15	=IFNA(IF(G15="HIGH",CONCATENATE("Area ", A14,","), ""),"")
N15,N17,N19,N21,N23,N25	N15	=IFNA(IF(G15="MODERATE",CONCATENATE("Area ", A14,","), ""),"")
J15	J15	=TEXTJOIN(" ", TRUE, M14:M53)
J17	J17	=TEXTJOIN(" ", TRUE, N14:N53)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M14:N73	Expression	=AND(COUNTIF($M$14:$N$43,M14)>1,$N14<>"")	text	NO

Thanks for looking

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[StephenCrump]

I know you have a solution that works for you ...

... but if you wanted to, you could dispense with your helper formulae:

	A	B	C	D	E	F	G	H	I	J
13										NOTES
14	1									HIGH
15							High			Area 1, Area 2 & Area 3
16	2									MODERATE
17							High			Area 4 & Area 5
18	3									LOW
19							High			-
20	4
21							Moderate
22	5
23							Moderate
24
Sheet1

Cell Formulas
Range	Formula
J15,J19,J17	J15	=LET(d,"Area "&FILTER($A$14:$A$22,$G$15:$G$23=J14),n,ROWS(d),t,TEXTJOIN(", ",1,d),IFERROR(IF(n<2,t,SUBSTITUTE(t,","," &",n-1)),"-"))

 

[Jaypee666]

Hi Fluff,

I am only getting around to properly implementing this on my worksheet, and I noticed that if there is only 1 entry in the range it returns a #Value error. I know from running through the evaluation it has something to do with the "-LEN" section of the string, but for the life of me I can't figure out how to over come it so that if there is only 1 Value, it simply returns that value.
I know this is probably something simply when you know what you're doing

Sample below

Pelco_Report_Creator_Feb-23.xlsx
	J	K	L	M	N
13	NOTES			High	Moderate
14	HIGH
15	Area 1, Area 2, Area 3, Area 4, Area 5, Area 6, Area 7, Area 8 & Area 10			Area 1,
16	MODERATE
17	No Areas to Action			Area 2,
18
19				Area 3,
20
21				Area 4,
22
23				Area 5,
24
25				Area 6,
26
27				Area 7,
28
29				Area 8,
30
31					Area 9,
32
33				Area 10,
34
35
Report-Clonkeen

Cell Formulas
Range	Formula
M14,M16,M18,M20,M22,M24,M26,M28,M30,M32,M34	M14	=IFNA(IF(G14="HIGH",CONCATENATE("Area ", A14,","), ""),"")
N14,N16,N18,N20,N22,N24,N26,N28,N30,N32,N34	N14	=IFNA(IF(G14="MODERATE",CONCATENATE("Area ", A14,","), ""),"")
M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35	M15	=IFNA(IF(G15="HIGH",CONCATENATE("Area ", A14,","), ""),"")
N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35	N15	=IFNA(IF(G15="MODERATE",CONCATENATE("Area ", A14,","), ""),"")
J15	J15	=IFERROR(SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(M14:M55,",","")),", "," & ",LEN(TEXTJOIN(", ",,SUBSTITUTE(M14:M55,",","")))-LEN(SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(M14:M55,",","")),",",""))),"No Areas to Action")
J17	J17	=IFERROR(SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(N14:N56,",","")),", "," & ",LEN(TEXTJOIN(", ",,SUBSTITUTE(N14:N56,",","")))-LEN(SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(N14:N56,",","")),",",""))),"No Areas to Action")

Thanks in advance for all your help on this.

 

[Peter_SSs]

Since this old thread has been revived anyway, I wondered if it possible that column M and/or N could have only 1 or zero actual values?
If so the accepted answer would return an error as shown in J17 below.
Alternative suggested just in case it is useful to OP or other readers.

23 02 24.xlsm
	J	K	L	M	N
13	NOTES			High	Moderate
14	HIGH
15	Area 1, Area 2 & Area 3			Area 1,
16	MODERATE
17	#VALUE!			Area 2,
18	Area 4
19				Area 3,
20
21					Area 4,
22
23
TEXTJOIN

Cell Formulas
Range	Formula
J15	J15	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(M14:M53,",","")),IF(ISNUMBER(FIND(",",t)),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))),t))
J17	J17	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(N14:N53,",","")),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))))
J18	J18	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(N14:N53,",","")),IF(ISNUMBER(FIND(",",t)),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))),t))

 

[Jaypee666]

Thank you

Since this old thread has been revived anyway, I wondered if it possible that column M and/or N could have only 1 or zero actual values?
If so the accepted answer would return an error as shown in J17 below.
Alternative suggested just in case it is useful to OP or other readers.

23 02 24.xlsm
	J	K	L	M	N
13	NOTES			High	Moderate
14	HIGH
15	Area 1, Area 2 & Area 3			Area 1,
16	MODERATE
17	#VALUE!			Area 2,
18	Area 4
19				Area 3,
20
21					Area 4,
22
23
TEXTJOIN

Cell Formulas
Range	Formula
J15	J15	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(M14:M53,",","")),IF(ISNUMBER(FIND(",",t)),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))),t))
J17	J17	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(N14:N53,",","")),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))))
J18	J18	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(N14:N53,",","")),IF(ISNUMBER(FIND(",",t)),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))),t))

Thank you, your J18 solution is exacty what I needed.

 

[Peter_SSs]

Thank you

Thank you, your J18 solution is exacty what I needed.

You're welcome.