Extract Date as Text

[How_Do_I]

How would I extract June 1918 from the following as text please?

Excel Workbook
	A
8	June 1918 Moved to Dublin.
Sheet1

 

[jeffreybrown]

Maybe...

=MID(A8,FIND(" ",A8)+1,4)

 

[Tetra201]

Try this:

=TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99),2),99))

 

[jeffreybrown]

Sorry, misread your post...

=TRIM(LEFT(A8,FIND("@",SUBSTITUTE(A8," ","@",2))))

 

[How_Do_I]

Thanks Jeff and Treta. I used Treta's formula with some other formulas I had as I hadn't seen your solution Jeff. A lot of my data for dates are in this format 01.07.1916 but I need to convert that to 01-Jul-16. So I needed a formula to find a dot before the first space. I notice your solution above Jeff gives an error to the day.month.year date format that some of my data is in so I'm sure I can improve the following formula now...

Excel Workbook
	C	D	E	F	G
11	OLD	Treta	NEW
12		Sep 1914	Sep 1914		Sep 1914 Formed
13		May 1917	May 1917		May 1917 Moved
14	08-Oct-1917	08.10.1917 Moved	08-Oct-1917		08.10.1917 Moved
15		Mar 1918	Mar 1918		Mar 1918 Disbanded.
Sheet1

 

[How_Do_I]

=IFERROR(TRIM(LEFT(G12,FIND("@",SUBSTITUTE(G12," ","@",2)))),TEXT(SUBSTITUTE(TRIM(SUBSTITUTE(CONCATENATE(MID(G12,1,10),"."),"."," "))," ","-"),"dd-mmm-yyyy")+0)

 

[FDibbins]

...A lot of my data for dates are in this format 01.07.1916 but I need to convert that to 01-Jul-16.

Are those actual (real) dates, or just "values" embedded in a text string?

If they are real dates, you wont "find" the dot, it does not exist - it is just formatting used by excel.

 

[How_Do_I]

Values embedded in text Ford...

 

[Tetra201]

As an alternative to your new formulas from Posts # 5 and # 6:

=IF(MID(REPLACE(TRIM(G12),4,2,""),3,2)="..",TEXT(MID(TRIM(G12),7,4)&"-"&MID(TRIM(G12),4,2)&"-"&LEFT(TRIM(G12),2),"dd-mmm-yyyy"),TRIM(LEFT(SUBSTITUTE(TRIM(G12)," ",REPT(" ",99),2),99)))

If you are sure there are no occasional double spaces and leading spaces in your strings, here is a lighter version:

=IF(MID(REPLACE(G12,4,2,""),3,2)="..",TEXT(MID(G12,7,4)&"-"&MID(G12,4,2)&"-"&LEFT(G12,2),"dd-mmm-yyyy"),TRIM(LEFT(SUBSTITUTE(G12," ",REPT(" ",99),2),99)))

 

[FDibbins]

Could you ever have a day when you have the month/yr combo?
1 Sep 2017
or just
Sep 2017
?