#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

 

[How_Do_I]

In case I am on the wrong tracks here, another question would be, how can I extract the "1 March 1918" from A1 please...

 

[FDibbins]

Do you have any other samples of what you have and what you want?

 

[FDibbins]

Based on your single sample, this works...
=FIND("@",SUBSTITUTE(B2," ","@",2))+1

 

[How_Do_I]

Hello Ford, I have various sources of data and some of the cells will have dates somewhere in the cell but there isn't a set pattern. I can deal with and extract 01/03/1914, 01.03.1914 and 01-Mar-1914 but what I can't extract is 01 March 1914 or 1 March 1914. My thoughts were, if I find "March" then three characters before "March" with TRIM will give the date or a random character and the date and from the end of "March" 5 characters with TRIM will give the year. Once I have the date it's not going to change so I haven't ruled out 12 helper columns, 1 for each month as I can delete it all once the dates are found. I have 9900 rows of data so too many to look through with the Mark I eyeball to find these dates...!

 

[FDibbins]

OK here is a way with 3 helpers and an answer column...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

2​

[/td][td]Apples 1 March 1918 2 left[/td][td]1[/td][td]March[/td][td]1918[/td][td]

Friday, March 01, 1918​

[/td][/tr]

[tr][td]

3​

[/td][td]blah 1 jan 2017[/td][td]1[/td][td]jan[/td][td]2017[/td][td]

Sunday, January 01, 2017​

[/td][/tr]

[tr][td]

4​

[/td][td]blah 5 June 2000[/td][td]5[/td][td]June[/td][td]2000[/td][td]

Monday, June 05, 2000​

[/td][/tr]
[/table]
B2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN()-1)+1,LEN($A2)))
copied across 2 more times
E2=DATEVALUE(B2&C2&D2)
formatted as needed - I used Long date

 

[How_Do_I]

Am I right is saying that solution assumes the day in my data is always after the first word? If so, that isn't always the case... I know LOL, I curse at this project on a daily basis! I'll get it sorted with a million and one helper columns but, I'm most grateful for your assistance tonight Ford and on a positive note, there will be data where your formula will work as the day will be after the first word!!! It's just a matter of using IFERROR and multiple formulas to get the date which will then be eventually hard coded anyway. Thanks again Ford.

 

[FDibbins]

Yes you are correct, and that's why I asked for some more samples

 

[AhoyNC]

This is an ugly array formula, but maybe does what you want?

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 1 February 1918 2 left	1 February 1918
4	Test sample 2 January 1918 and some words at the end	2 January 1918
5	2 January 1918	2 January 1918
Sheet

 

[How_Do_I]

Thanks Ahoy, that solution is working and is going to help me find some dates but it only seems to be working with single digit dates.