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 .

A	B	C	D	E	Result
#N/A	#N/A	Sydney	#N/A	#N/A	Sydney
Paramatta	#N/A	#N/A	#N/A	#N/A	Paramatta
#N/A	#N/A	#N/A	Harris Park	#N/A	Harris Park

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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.

A	B	C	D	E	Result
#N/A	#N/A	Sydney	#N/A	#N/A	Sydney
Sydney	#N/A	Paramatta	#N/A	#N/A	Paramatta
#N/A	Westmead	#N/A	Sydney	#N/A	Sydney

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.

Names	Grades - File 1	Grades - File 2	Grades - File 3	Grades - File 4	Result
Ayesha	#N/A	6F	#N/A	#N/A	6F
Matt	1B	#N/A	#N/A	2A	1B, 2A
David	2A	#N/A	3C	#N/A	2A, 3C

<tbody>
</tbody>

 

[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.