I have a column with some info displayed like that:

I have a formula to extract the year in the above column that is:

=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE([@[Product Name]]," ","</m><m>")&"</m></k>","//m[.=number() and string-length()=4]"),"")

The problem with this formula is that it works fine with the first case, but it gives me a #SPILL! Error on the other two cases. My ideal output would be:

• Basically, for the first case, just return the 4 digits. EVERY time that I only have one sequence of 4 digits, I want to return that sequence.
• For the second case, I want to return ONLY the second year. EVERY time I have 2 sequences of 4 digits, I want to return ONLY the second year.
• For the third case, I want to return nothing. EVERY time I have more than 2 sequences of 4 digits, I want to return blank.

The last thing I tried to add was position()>5 and that would cut off the 1995 in the second example, but I would continue having the Error on the third example. Also, my list is quite huge, and I am not sure if the position()>5 thing would work for ALL products that fall in the same second example.

I am not very good with XPATH, so any help would be greatly appreciated. Thank you!

CodePudding user response：

You can expand the xpath for sure if you so desire. However, I'd rewrite it a little bit. Each predicate, the structure between the opening and closing square brackets, is a filter of a given nodelist. To write multiple of these structures is in fact

Formula in B2:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[.*0=0][string-length()=4][position() = last() and (position() = 1 or position() mod 2 = 0)]"),"")

Whilst the above would work for Excel 2013 and higher^‡‡‡, you do talk about spilled behaviour. If you happen to work with the current channel in ms365 you could also try:

=LET(x,TEXTSPLIT(A2," "),y,--FILTER(x,ISNUMBER(-(x&"**0"))*(LEN(x)=4),{1,2,3}),z,COUNT(y),IF(OR(z=1,MOD(z,2)=0),TAKE(y,,-1),""))

^{‡ _{Note that you can be more strict about this if you'd wish to validate that a year is between say 1900-2050 or so. One could replace the 1st and 2nd predicate with [.*1>1899][.*1<2051].}}

^{‡‡ _{Note that the order or writing your and/or statements in xpath do matter. We need to use explicit parentheses to control the precedence. See}}