#value!

[How_Do_I]

Hello, my B2 is returning the value error if I look at Logical1 in the Formula Bar and press F9 I see #VALUE !,#VALUE !,10. If there anyway I can get B2 to return the 10 please?

Excel Workbook
	A	B	C
2	Apples 1 March 1918 2 left	#VALUE !	10
Sheet1

 

[AhoyNC]

OK, try this: NOT quit working didn't pick up last digit in year.
Enter with CTRL-SHIFT-ENTER.

	A	B
Apples 1 March 1918 2 left
Number of words before date 12 February 1918 2 left
Test sample 24 January 1918 and some words at the end

<colgroup><col style="width:30px; "><col style="width:383px;"><col style="width:132px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1 March 191[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]12 February 191[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]24 January 191[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]2 January 1918[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2 January 1918[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]16 March 1918[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]16 March 191[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=TRIM(MID(MID(A2,MAX(IFERROR(SEARCH({"January","February","March"},A2),0)-3,1),LEN(A2)),1,FIND(CHAR(7),SUBSTITUTE(MID(A2,MAX(IFERROR(SEARCH({"January","February","March"},A2),0)-2),LEN(A2))," ",CHAR(7),2))+4))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

This seems to work now.
Enter with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B
1
2	Apples 1 March 1918 2 left	1 March 1918
3	Number of words before date 12 February 1918 2 left	12 February 1918
4	Test sample 24 January 1918 and some words at the end	24 January 1918
5	2 January 1918	2 January 1918
6	16 March 1918	16 March 1918
Sheet

 

[How_Do_I]

Thanks for spending time trying to get this working Ahoy, I'm most grateful. Your formula is partially working here, I can't get a return for the cells with only a date in. I've tried changing the format of these cells but nothing so, what date format have you used pleased?

 

[AhoyNC]

In my example cells A5 and A6 were entered as text. Are your cells with just dates actual Excel dates (numeric)?

 

[How_Do_I]

No, always text in my workbook. When I pasted your example in to a different workbook they became numerical but, I've changed them to text now, thanks again/

 

[AhoyNC]

You're welcome. Hope you got everything working. I think Excel usually will try and enter a pasted value the looks like date as a numeric date which probably gave you a #VALUE error in my example.
Thanks for the feedback.