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]

.. i am not sure why this is still giving me #Value in each cell.

Does this make a difference?

=IF(LEN(A1)=9,EOMONTH("1 "&SUBSTITUTE(A1,",",""),0),"")

A8 should not return a date as the string in more than 9 characters long.

Is it? What is the 10th character in the cell?

 

[AMIT2179]

This works, Thanks much. 10th Character?

 

[Peter_SSs]

This works, Thanks much.

Good news.

10th Character?

You said "No A8 should not return a date as the string in more than 9 characters long." but I (& Excel) could only count 9 so I was asking what you thought the 10th character was in A8 of my post 19 screen shot.

 

[AMIT2179]

Ohh OK. I may have miscalculated.

Can you also help me with one more solution.

I have below table with columns A to E and Result in column F. I am looking for a formulae in Column F which gives me a result in case it finds a string in any of the cells of a particular row except #NA .
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[TD="class: xl63, width: 64"]D[/TD]
[TD="class: xl63, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]Result[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64"]Sydney[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl66"]Sydney[/TD]
[/TR]
[TR]
[TD="class: xl64"]Paramatta[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl66"]Paramatta[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl64"]Harris Park[/TD]
[TD="class: xl64, align: center"]#N/A[/TD]
[TD="class: xl66"]Harris Park[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

try this

<b>Lookup</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:79px;" /><col style="width:48px;" /><col style="width:63px;" /><col style="width:84px;" /><col style="width:48px;" /><col style="width:84px;" /></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></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">Sydney</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">Sydney</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Paramatta</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; ">Paramatta</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</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; ">Harris Park</td><td style="font-size:10pt; ">#N/A</td><td style="font-size:10pt; ">Harris Park</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 >F1</td><td >=LOOKUP("ZZZ",A1:E1)</td></tr></table></td></tr></table>

 

[AMIT2179]

Wow. You make it look so simple and i keep on adding the complexities. How can we get all values where there are more than 1? Like in below example.

[TABLE="width: 394"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Sydney[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Sydney[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Paramatta[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Paramatta[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD]Westmead[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Sydney[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Sydney[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Perhaps you had better explain again as the formula I posted already gives the results you have shown in post 26.

What version of Excel are you using?
How many columns do you really have?

 

[AMIT2179]

Actually i am working on the data and extraction values by applying v lookup in various files. So for eg: Column A has names against which i want to find their grades. These names are in various files (but not all names in all files). So i put a vlookup in column B using file 1. I get some data in column B from file 1. Now i use file 2 and get the results in column c. Similarly using File 3 and getting results in Column D. So i have data (in B1, C1, D1, E1 etc) against name in A1. Similarly for A2, A3 etc. Now one name might have more than one data like in Row 2 below where we have 2 data 1B and 2A. It might have 3 data as well depending on how many files i have used to extract grades. So i am looking for a result in last column which gives me all the data (from column B - Column E or Column G) against value in column A.
[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Names[/TD]
[TD="class: xl63, width: 64"]Grades - File 1[/TD]
[TD="class: xl63, width: 64"]Grades - File 2[/TD]
[TD="class: xl63, width: 64"]Grades - File 3[/TD]
[TD="class: xl63, width: 64"]Grades - File 4[/TD]
[TD="class: xl63, width: 64"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Ayesha[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]6F[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]6F[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Matt[/TD]
[TD="class: xl63, width: 64"]1B[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]2A[/TD]
[TD="class: xl63, width: 64"]1B, 2A[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]David[/TD]
[TD="class: xl63, width: 64"]2A[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]3C[/TD]
[TD="class: xl63, width: 64"]#N/A[/TD]
[TD="class: xl63, width: 64"]2A, 3C[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

OK, that's clearer about what you want, but not clearer on the answers to my other 2 questions.

 

[AMIT2179]

Oops sorry. We use Excel 2016 and i have around 5 columns.