Extract the Month/s from a given text value

[ExcelNewbie2020]

I would like to extract the months per year from the given values in column A with different kind of scenarios.

Book1
	A	B	C
2	GIVEN	EXPECTED RESULT
3	YYYYMM	2020	2021
4	202001	JANUARY
5	202101		JANUARY
6	202001 & 202003	JANUARY & MARCH
7	202101, 202102, 202103		JANUARY, FEBRUARY, MARCH
8	202101 - 202110		JANUARY - OCTOBER
9	202001, 202004 - 202005 & 202012	JANUARY, APRIL - MAY & DECEMBER
Sheet2

 

[Micron]

Are you able to use vba code? If so, I think some things need clarification:
- when months are consecutive (04 - 05) can't B9 above be JANUARY, APRIL, MAY, DECEMBER ? Easier to use one format instead of dashes and ampersands as well.
- what if two values are 202004 - 202006? Is that [ April, June ] (I would hope) or [ April, May, June ] ? Or maybe that will never be the case?

 

[hagia_sofia]

Hello, the following could work for the example provided but of course things might be a little bit more complicated within you data:

=LET(
y,B$3,
s,$A4,
d,REGEXEXTRACT(s,"\d{6}",1),
m,UPPER(TEXT(DATE(LEFT(d,4),RIGHT(d,2),1),"mmmm")),
j,IFERROR(REGEXEXTRACT(s,"\W{2,3}",1),""),
IF(ISNUMBER(SEARCH(y,s)),TEXTJOIN(j,,m),""))

 

[djclements]

Another possibility, based on your sample data:

=LET(
    data, SUBSTITUTE(A4:A9,","," ,"),
    n, SEQUENCE(,MAX(LEN(data)-LEN(SUBSTITUTE(data," ",)))+1),
    a, IFERROR(TEXTBEFORE(TEXTAFTER(" "&data&" "," ",n)," "),""),
    b, IFERROR(UPPER(TEXT(RIGHT(a,2)*29,"mmmm")),a),
    m, SUBSTITUTE(BYROW(b,LAMBDA(r,TEXTJOIN(" ",1,r)))," ,",","),
    y, VALUE(LEFT(TAKE(a,,1),4)),
    DROP(PIVOTBY(SEQUENCE(ROWS(data)),y,m,SINGLE,0,0,,0),,1)
)

Please note, this assumes your actual data won't ever contain multiple years within a single delimited string.

 

[Cubist]

One more option:

Book1
	A	B	C
1
2	GIVEN	EXPECTED RESULT
3	YYYYMM	2020	2021
4	202001	JANUARY
5	202101		JANUARY
6	202001 & 202003	JANUARY & MARCH
7	202101, 202102, 202103		JANUARY , FEBRUARY , MARCH
8	202101 - 202110		JANUARY - OCTOBER
9	202001, 202004 - 202005 & 202012	JANUARY , APRIL - MAY & DECEMBER
10	202101 - 202110		JANUARY - OCTOBER
Sheet3

Cell Formulas
Range	Formula
B4:C10	B4	=LET(a,A4, d,{"&",",","-"},m,MID(a,SEQUENCE(LEN(a)),1), delim,IFERROR(TOCOL(IFS(ISNUMBER(XMATCH(m,d)),m),2),""), t,TRIM(TEXTSPLIT(a,d)),MAP($B$3:$C$3,LAMBDA(x,TEXTJOIN(" "&delim&" ",,FILTER(UPPER(TEXT(RIGHT(t,2)&"/1900","mmmm")),--LEFT(t,4)=x,"")))) )
Dynamic array formulas.

 

[HighAndWilder]

I would like to extract the months per year from the given values in column A with different kind of scenarios.

Book1
	A	B	C
2	GIVEN	EXPECTED RESULT
3	YYYYMM	2020	2021
4	202001	JANUARY
5	202101		JANUARY
6	202001 & 202003	JANUARY & MARCH
7	202101, 202102, 202103		JANUARY, FEBRUARY, MARCH
8	202101 - 202110		JANUARY - OCTOBER
9	202001, 202004 - 202005 & 202012	JANUARY, APRIL - MAY & DECEMBER
Sheet2

Another option is to call a UDF placed in a main code module.

An array is returned by the function which spills out into the adjacent cells as appropriate.

This also assumes that your actual data won't ever contain multiple years within a single delimited string.

Place this function call in cell B3 and change the range as appropriate.

It places the column headings (years) as appropriate.

=fncExtractMonths(A4:A9)

Public Function fncExtractMonths(rng As Range)
Dim arr() As Variant
Dim i As Integer
Dim arrYears() As Variant
Dim varYear As Variant
Dim m As Integer
Dim res() As Variant
Dim y As Integer
  
  arrYears = Evaluate("SORT(UNIQUE(LEFT(WRAPCOLS(" & rng.Address & ",20),4)))")

  arr = rng

  ReDim res(1 To UBound(arr) + 1, 1 To UBound(arrYears))
  
  For Each varYear In arrYears
  
    y = y + 1
    
    res(1, y) = varYear
      
    For i = 1 To UBound(arr)
      res(i + 1, y) = ""
      For m = 1 To 12
        If InStr(1, arr(i, 1), varYear & Right("0" & m, 2), vbTextCompare) > 0 Then
          arr(i, 1) = Replace(arr(i, 1), varYear & Right("0" & m, 2), Format("01/" & Right("0" & m, 2) & "/" & varYear, "MMMM"))
          res(i + 1, y) = arr(i, 1)
        End If
      Next m
    Next i
  
  Next varYear
    
  fncExtractMonths = res

End Function