Avoid Repetition/Mismatch in TEXTJOIN of Cell Contents and a Named Range

[rfhandel]

Sorry for my ignorance in formulas. I use what I can beg, borrow, or steal, but I do not do a very good job of figuring them out on my own.

I am trying to extract metadata from a cell that is specifically relevant to various media used in an image. Each metadata term is separated by a delimiter (i.e. ";").

I have managed to use a TEXTJOIN formula to extract the data from a named range as demonstrated in the formula and screenshot below, with delimiters dividing the extracted text;

=TEXTJOIN(",",TRUE,IF(ISNUMBER(FIND(Table15[Media],D4)),MID(D4,FIND(Table15[Media],D4),LEN(Table15[Media])),""))

however, as indicated by the highlighted text below, the formula also extracts text that is not an exact match from the delimited text to the data entered in the named range (i.e. Acrylic AND Acrylic Gouache vs just Acrylic Gouache). How can I get the formula to read an exact match to the cells in the named range with the text from the target cell.

Secondly, but of less importance, how could I add a conjunction (i.e. "and") to the last extracted term in addition to or replacing the comma delimiter?

 

[Cubist]

Can you post your data in a copy-able format? The forum has a XL2BB tool.

 

[rfhandel]

Array Formula.xlsx
	D	E	F	G	H
1
2	Acrylic; Canvas; Dry Pigment; Face(s); Grass; Leaves; Media; Nature; Painting; Roots; Silver leaf gilding; Sulphur oxidized patina; Theme; Woman; Woman(en)		Acrylic, Dry Pigment, Silver leaf gilding, Sulphur oxidized patina		TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND(Table15[Media],D2)),MID(D2,FIND(Table15[Media],D2),LEN(Table15[Media])),""))
3
4	Acrylic Gouache; Angel; Fiber board; Flower; Media; Music; Painting; Theme; Trumpet		Acrylic Gouache, Acrylic		TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND(Table15[Media],D4)),MID(D4,FIND(Table15[Media],D4),LEN(Table15[Media])),""))
5
6			Media
7			24k Gold & Silver leaf gilding
8			24k Gold leaf gilding
9			Acrylic Gouache
10			Acrylic
11			Dry Pigment
12			Gansai Watercolor
13			Silver leaf gilding
14			Sulphur oxidized patina
15			Sumi ink
16			Kakushibu (persimmon tannin)
17			Washi paper
18			Collage
19
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND(Table15[Media],D2)),MID(D2,FIND(Table15[Media],D2),LEN(Table15[Media])),""))

 

[Cubist]

Try:

=LET(t,TEXTSPLIT(D2,,"; "),TEXTJOIN(", ",,FILTER(t,ISNUMBER(XMATCH(t,Table15[Media])))))

 

[Fluff]

How about

=TEXTJOIN("; ",,FILTER(Table15[Media],ISNUMBER(XMATCH(Table15,TEXTSPLIT(D2,"; ")))))

 

[rfhandel]

Try:

=LET(t,TEXTSPLIT(D2,,"; "),TEXTJOIN(", ",,FILTER(t,ISNUMBER(XMATCH(t,Table15[Media])))))

I am a little less clear on how this one works, but it indeed works. Thank you! Any possibility on how I can replace the comma delimiter with "and" for the last two extracted terms (i.e. "Acrylic, Dry Pigment, Silver leaf gilding and Sulphur oxidized patina)

 

[Fluff]

How about

=LET(f,FILTER(Table15[Media],ISNUMBER(XMATCH(Table15,TEXTSPLIT(D4,"; ")))),IF(ROWS(f)=1,f,TEXTJOIN(", ",,DROP(f,-1))&" and "&TAKE(f,-1)))

 

[rfhandel]

How about

=TEXTJOIN("; ",,FILTER(Table15[Media],ISNUMBER(XMATCH(Table15,TEXTSPLIT(D2,"; ")))))

This one works equally as well, and I am almost clear on how it works. Same question, however: any clue on how to replace the last comma delimiter with "and" with the extracted text (i.e. "Acrylic, Dry Pigment, Silver leaf gilding and Sulphur oxidized patina")

 

[Fluff]

See post#7

 

[rfhandel]

How about

=LET(f,FILTER(Table15[Media],ISNUMBER(XMATCH(Table15,TEXTSPLIT(D4,"; ")))),IF(ROWS(f)=1,f,TEXTJOIN(", ",,DROP(f,-1))&" and "&TAKE(f,-1)))

Perfect!!! Thank you so much! I am so thankful I discovered this forum; you all have already saved me hours of research and work!