Extracting a date from a cell which has a particular value

[AMIT2179]

I have 3 cells in excel in same column. One has value "Dec, 2014", second one has value "NA_Dec'14" and the third one has value "NA_". As you can see 3 cells have 3 different values. I am looking to extract a date either first day of the month or last day of the month from the second cell which has value "NA_Dec'14". So the output should look like either 12/01/14 or 12/31/14 or anyday of the Dec 14. Please can you help.

 

[Peter_SSs]

We use Excel 2016 ..

If that is the stand-alone version of Excel 2016 then you would need something like the F2 formula. If it is through Office 365 you could use the array formula in G2.

<b>Lookup (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:118px;" /><col style="width:117px;" /><col style="width:125px;" /><col style="width:117px;" /><col style="width:77px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Names</td><td style="font-size:10pt; ">Grades - File 1</td><td style="font-size:10pt; ">Grades - File 2</td><td style="font-size:10pt; ">Grades - File 3</td><td style="font-size:10pt; ">Grades - File 4</td><td style="font-size:10pt; ">Result</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Ayesha</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">6F</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">6F</td><td style="font-size:10pt; ">6F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Matt</td><td style="font-size:10pt; ">1B</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; text-align:right; ">2A</td><td style="font-size:10pt; ">1B, 2A</td><td style="font-size:10pt; ">1B, 2A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">David</td><td style="font-size:10pt; text-align:right; ">2A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">3C</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">2A, 3C</td><td style="font-size:10pt; ">2A, 3C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Sue</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=REPLACE(IFNA<span style=' color:008000; '>(", "&B2,"")</span>&IFNA<span style=' color:008000; '>(", "&C2,"")</span>&IFNA<span style=' color:008000; '>(", "&D2,"")</span>&IFNA<span style=' color:008000; '>(", "&E2,"")</span>,1,2,"")</td></tr><tr><td >G2</td><td >{=TEXTJOIN(", ",1,IFNA<span style=' color:008000; '>(B2:E2,"")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[AMIT2179]

Worked fine. Thanks. Another problem i ran into:

Basically there are many rows and columns of data. I am looking for a formulae in a column which reads contents of one column and if it finds the following words it gives me a "Yes" otherwise a "No".
The words i am looking at are:

Chens
Jin-Xing
Moh Kok Heng
Svalinn
Tan Siow Hoon Terry
AIA Singapore

<tbody>
</tbody>

 

[Peter_SSs]

But this latest question is a duplicate to https://www.mrexcel.com/forum/excel-questions/1114680-if-cell-contains-text-list-then-return-value-post5369606.html

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.