extract month from Txt

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
I have text data in col A


I need to extract the month in Col B


I have tried to set up a formula to do this but where the number of characters changes for a particular month, it does not work


It would be appreciated if someone could assist me



<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Description</td><td style=";">Month</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sales Data for February 2019</td><td style=";">February</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sales Data for March 2019</td><td style=";">March 20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=MID(<font color="Blue">A2,FIND(<font color="Red">"for",A2</font>)+4,8</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=MID(<font color="Blue">A3,FIND(<font color="Red">"for",A3</font>)+4,8</font>)</td></tr></tbody></table></td></tr></table><br />
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
If all dates are in 2019, you could use
=SUBSTITUTE(MID(A2,FIND("for",A2)+4,100)," 2019","")
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Description</td><td style=";">Month</td><td style="text-align: right;;"></td><td style=";">Month</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sales Data for February 2019</td><td style="background-color: #FFFF00;;">February</td><td style="text-align: right;;"></td><td style=";">January</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sales Data for March 2019</td><td style="background-color: #FFFF00;;">March</td><td style="text-align: right;;"></td><td style=";">February</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">March</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">April</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">May</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">June</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">July</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">August</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">September</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">October</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">November</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">December</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">125</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99E+307,SEARCH(<font color="Red">$D$2:$D$13,A2</font>),$D$2:$D$13</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
Alternately
=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("for",A2)+4,100)," ",REPT(" ",100)),100))
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If it always prefixed with "Sales data for" and nothing else then here is another option.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Description</td><td style=";">Month</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sales Data for February 2019</td><td style="background-color: #FFFF00;;">February</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sales Data for March 2019</td><td style=";">March</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=TEXT(<font color="Blue">REPLACE(<font color="Red">A2,1,15,1</font>),"mmmm"</font>)</td></tr></tbody></table></td></tr></table><br />
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help FormR
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks for the help Alan
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help Fluff

Please explain how your formula works
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
Which one?
I would recommend using the formula from FormR, it's a lot neater :)
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Agree, it easier to follow


Please explain this part of your formula


Code:
," ",REPT(" ",100)),100))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top