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

 

[StephenCrump]

Like this perhaps?

	A	B	C	D	E
1	A
2
3		D			G
4				E
5	B
6					H
7
8				F	I
9					J
10	C
11
12	A, B & C	D		E & F	G, H, I & J
Sheet1

Cell Formulas
Range	Formula
A12:E12	A12	=LET(d,A1:A10,n,COUNTA(d),t,TEXTJOIN(", ",1,d),IF(n<2,t,SUBSTITUTE(t,","," &",n-1)))

 

[Fluff]

Another option

Fluff.xlsm
	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
Data

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

 

[Jaypee666]

Thank you both,

I am going to go with Fluffs, as it worked immediately. For some reason I can't see, StephenCrumps is not returning as expected, as seen below

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	StephenCrump			Area 3,
20	Area 1,, Area 2,, Area 3,
21	Area 4,, Area 5,				Area 4,
22
23	Fluff				Area 5,
24	Area 1, Area 2 & Area 3
25	Area 4 & Area 5
26
Report-1

Cell Formulas
Range	Formula
M14,M16,M18,M20,M22,M24,M26	M14	=IFNA(IF(G14="HIGH",CONCATENATE("Area ", A14,","), ""),"")
N14,N16,N18,N20,N22,N24,N26	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)
J20	J20	=LET(d,M14:M25,n,COUNTA(d),t,TEXTJOIN(", ",1,d),IF(n<2,t,SUBSTITUTE(t,","," &",n-1)))
J21	J21	=LET(d,N14:N25,n,COUNTA(d),t,TEXTJOIN(", ",1,d),IF(n<2,t,SUBSTITUTE(t,","," &",n-1)))
J24	J24	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(M14:M25,",","")),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))))
J25	J25	=LET(t,TEXTJOIN(", ",,SUBSTITUTE(N14:N25,",","")),SUBSTITUTE(t,", "," & ",LEN(t)-LEN(SUBSTITUTE(t,",",""))))

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

If you can see what is wrong with StephenCrumps let me know please, only change I made was to the Range.

 

[Jaypee666]

Also, do you know is there a way to do this in Sheets?

 

[Fluff]

Stephen's doesn't work as you have formulae in M14:M25 which is causing the problem.

 

[Fluff]

For Sheets you could try

=SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(M14:M25,",","")),", "," & ",LEN(TEXTJOIN(", ",,SUBSTITUTE(M14:M25,",","")))-LEN(SUBSTITUTE(TEXTJOIN(", ",,SUBSTITUTE(M14:M25,",","")),",","")))

 

[Jaypee666]

Stephen's doesn't work as you have formulae in M14:M25 which is causing the problem.

I was working off the presumption that his covers the range A2:A10 where the data is, I want to use the range M14:M25 where my data is. Does it matter where the formula is in relation to the range? It has me confused beyond belief at the moment.

 

[Fluff]

Stephen's formula is using COUNTA to work out how many values you have, but that counts all cells with a formula, hence the problem.

 

[Jaypee666]

Stephen's formula is using COUNTA to work out how many values you have, but that counts all cells with a formula, hence the problem.

Yes, of course, sorry I read your initial response incorrectly, thanks for the clarification