Split text into columns with different delimiters

[probexcel]

I am having a little bit of trouble trying to come with a solution for my problem, I have a table where one of the columns has texts like:

This is one text 2019

This is one text 2020

This is one product 2018 (6bl

This is one product 2020 (6bl)

This is a pain 2020 (6bl)

This is a pain 2021 (6bl)

This is hard (6bl) 2017

This is hard (6bl) 2018

This is lack of creativity (12x6) 2014

This is lack of creativity (12x6) 2015

This is the last example (With explanation) and more text 2020

This is the last example (With explanation) and more text 2022

What I need to do is to compare the text part and the (6bl) part. and if everything is the same but the year is different, in the new column I need to flag them. My problem is, I couldn't find a good way to extract the year out of this string, and also, if you realize in the text that is in red, some of the parenthesis are not being closed. The table is huge, so I just would like some idea on how to tackle this project.

My final output could be something like:

This is one text	2019
This is one text	2020
This is one product	2018	6bl
This is one product	2020	6bl
This is a pain	2020	6bl
This is a pain	2021	6bl
This is hard	2017	6bl
This is hard	2018	6bl
This is lack of creativity	2014	12x6
This is lack of creativity	2015	12x6
This is the last example (With explanation) and more text	2020
This is the last example (With explanation) and more text	2022

Is there any way to achieve that through formulas or VBA?

 

[Fluff]

With that sample, how about

Fluff.xlsm
	A	B	C
1
2	This is one text 2019	2019
3	This is one text 2020	2020
4	This is one product 2018 (6bl	2018	6bl
5	This is one product 2020 (6bl)	2020	6bl
6	This is a pain 2020 (6bl)	2020	6bl
7	This is a pain 2021 (6bl)	2021	6bl
8	This is hard (6bl) 2017	2017	6bl
9	This is hard (6bl) 2018	2018	6bl
10	This is lack of creativity (12x6) 2014	2014	12x6
11	This is lack of creativity (12x6) 2015	2015	12x6
12	This is the last example (With explanation) and (6bl) text 2020	2020	6bl
13	This is the last example (With explanation) and more text 2022	2022
Lists

Cell Formulas
Range	Formula
B2:B13	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number()]")
C2:C13	C2	=IFERROR(SUBSTITUTE(SUBSTITUTE(FILTERXML("<k><m>"&SUBSTITUTE(A2&")"," ","</m><m>")&"</m></k>","//m[contains(.,'(') and contains(.,')')]"),")",""),"(",""),"")

 

[probexcel]

With that sample, how about

Fluff.xlsm
	A	B	C
1
2	This is one text 2019	2019
3	This is one text 2020	2020
4	This is one product 2018 (6bl	2018	6bl
5	This is one product 2020 (6bl)	2020	6bl
6	This is a pain 2020 (6bl)	2020	6bl
7	This is a pain 2021 (6bl)	2021	6bl
8	This is hard (6bl) 2017	2017	6bl
9	This is hard (6bl) 2018	2018	6bl
10	This is lack of creativity (12x6) 2014	2014	12x6
11	This is lack of creativity (12x6) 2015	2015	12x6
12	This is the last example (With explanation) and (6bl) text 2020	2020	6bl
13	This is the last example (With explanation) and more text 2022	2022
Lists

Cell Formulas
Range	Formula
B2:B13	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number()]")
C2:C13	C2	=IFERROR(SUBSTITUTE(SUBSTITUTE(FILTERXML("<k><m>"&SUBSTITUTE(A2&")"," ","</m><m>")&"</m></k>","//m[contains(.,'(') and contains(.,')')]"),")",""),"(",""),"")

Ohh my gosh, thank you so much. That's basically what I needed. One question, for the formula that extracts the year, cell B2, I have one row that is like that:
This is a text number 2 2020

This is giving me a SPILL error because the spill range isn't blank, how can I fix that to either return only the number with four digits, or to always get the second number? Because I just checked, and every line that has a number like that, the year comes after it.

 

[Fluff]

Will there only ever be one 4 digit number?

 

[probexcel]

Will there only ever be one 4 digit number?

Yes, and always starting with 2.

 

[Fluff]

Ok, how about

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