extract date from string

[doriannjeshi]

Hello,

I need to extract the dates from a string but I am having mixed results

the date format I need is dd/mm/yyyy and possibly the hours in another column

Feb 6, 1930 1:21 PM Mars enters Aquarius

Mar 17, 1930 12:55 AM Mars enters Pisces

Apr 24, 1930 12:27 PM Mars enters Aries

June 2, 1930 10:15 PM Mars enters Taurus

July 14, 1930 7:54 AM Mars enters Gemini

Aug 28, 1930 6:27 AM Mars enters Cancer

 

[doriannjeshi]

Yes this it, thank you !
Thanks to all for coming up with solutions!
Peter_SSs, rlv01, shinigamilight !

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

The formula you show there is nothing like the one you posted & said you had tested with 100% success.

More than that, the new formula still has not been tested on the actual sample data provided in posts 1, 3 & 6. Here is a section that I have tested.

doriannjeshi.xlsm
	F		H
1	Feb 6, 1930 1:21 PM Mars enters Aquarius		1:21 PM
2	Mar 17, 1930 12:55 AM Mars enters Pisces		12:55 AM
3	Apr 24, 1930 12:27 PM Mars enters Aries		12:27 PM
4	June 2, 1930 10:15 PM Mars enters Taurus		10:15 PM
5	July 14, 1930 7:54 AM Mars enters Gemini		7:54 AM
6	Aug 28, 1930 6:27 AM Mars enters Cancer		6:27 AM
7	Apr 2, 2011 12:51 AM Mars enters Aries		#VALUE!
8	Aug 3, 2011 5:22 AM Mars enters Cancer		#VALUE!
9	July 3, 2012 8:32 AM Mars enters Libra		#VALUE!
10	Oct 6, 2012 11:21 PM Mars enters Sagittarius		#VALUE!
11	Feb 1, 2013 8:54 PM Mars enters Pisces		#VALUE!
12	Dec 7, 2013 3:41 PM Mars enters Libra		#VALUE!
13	Dec 4, 2014 6:57 PM Mars enters Aquarius		#VALUE!
14	Jan 12, 2015 5:20 AM Mars enters Pisces		5:20 AM
15	Feb 19, 2015 7:11 PM Mars enters Aries		7:11 PM
16	Mar 31, 2015 12:26 PM Mars enters Taurus		12:26 PM
17	May 11, 2015 10:40 PM Mars enters Gemini		10:40 PM
18	Jun 24, 2015 9:33 AM Mars enters Cancer		9:33 AM
19	Aug 8, 2015 7:32 PM Mars enters Leo		#VALUE!
20	Sep 24, 2015 10:18 PM Mars enters Virgo		10:18 PM
21	Nov 12, 2015 4:41 PM Mars enters Libra		4:41 PM
22	Jan 3, 2016 9:32 AM Mars enters Scorpio		#VALUE!
23	Mar 5, 2016 9:29 PM Mars enters Sagittarius		#VALUE!
24	Aug 2, 2016 1:49 PM Mars enters Sagittarius		#VALUE!
Dates Times

Cell Formulas
Range	Formula
H1:H24	H1	=VALUE(TRIM(REPLACE(MID(F1,FIND("@",SUBSTITUTE(F1," ","@",3)),95),FIND("Mars",MID(F1,FIND("@",SUBSTITUTE(F1," ","@",3)),95)),95,"")))

 

[Dave Patton]

Please see

doriannjeshi's original post on this challenge​

Calculate date

Hello, Is there a formula to calculate the specific sign knowing when a sign enters the date ? period sign date sign 23/01/1930 Aquarius 08/12/1930 ? 16/02/1930 Pisces 04/05/1930 ? 12/03/1930 Aries 06/01/1930 ? 05/04/1930 Taurus - 30/04/1930 Gemini - 25/05/1930...

www.mrexcel.com

Information on the source information, system setup, etc. is not clear.
The Regional Settings may or may not be USA.

I used Search and Replace to remove excess spaces and then used a UDF.
Could the UDF be enhanced to eliminate
the TEXTBEFORE(A2," ",5)
or the
LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",5)))

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(S) + TimeValue(S)
End Function

T202211x.xlsm
	A	B	C
1		With 365
2	Apr 2, 2011 12:51 AM Mars enters Aries	2011-Apr-02 00:51	2011-Apr-02 00:51
3	Aug 3, 2011 5:22 AM Mars enters Cancer	2011-Aug-03 05:22	2011-Aug-03 05:22
4	July 3, 2012 8:32 AM Mars enters Libra	2012-Jul-03 08:32	2012-Jul-03 08:32
5	Oct 6, 2012 11:21 PM Mars enters Sagittarius	2012-Oct-06 23:21	2012-Oct-06 23:21
6	Feb 1, 2013 8:54 PM Mars enters Pisces	2013-Feb-01 20:54	2013-Feb-01 20:54
7	Dec 7, 2013 3:41 PM Mars enters Libra	2013-Dec-07 03:41	2013-Dec-07 03:41
8	Dec 4, 2014 6:57 PM Mars enters Aquarius	2014-Dec-04 06:57	2014-Dec-04 06:57
9	Jan 12, 2015 5:20 AM Mars enters Pisces	2015-Jan-12 05:20	2015-Jan-12 05:20
10	Feb 19, 2015 7:11 PM Mars enters Aries	2015-Feb-19 07:11	2015-Feb-19 07:11
11	Mar 31, 2015 12:26 PM Mars enters Taurus	2015-Mar-31 12:26	2015-Mar-31 12:26
12	May 11, 2015 10:40 PM Mars enters Gemini	2015-May-11 22:40	2015-May-11 22:40
13	Jun 24, 2015 9:33 AM Mars enters Cancer	2015-Jun-24 09:33	2015-Jun-24 09:33
14	Aug 8, 2015 7:32 PM Mars enters Leo	2015-Aug-08 07:32	2015-Aug-08 07:32
15	Sep 24, 2015 10:18 PM Mars enters Virgo	2015-Sep-24 22:18	2015-Sep-24 22:18
16	Nov 12, 2015 4:41 PM Mars enters Libra	2015-Nov-12 16:41	2015-Nov-12 16:41
17	Jan 3, 2016 9:32 AM Mars enters Scorpio	2016-Jan-03 09:32	2016-Jan-03 09:32
18	Mar 5, 2016 9:29 PM Mars enters Sagittarius	2016-Mar-05 09:29	2016-Mar-05 09:29
19	Aug 2, 2016 1:49 PM Mars enters Sagittarius	2016-Aug-02 01:49	2016-Aug-02 01:49
20	Nov 9, 2016 12:51 AM Mars enters Aquarius	2016-Nov-09 00:51	2016-Nov-09 00:51
21	Mar 9, 2017 7:34 PM Mars enters Taurus	2017-Mar-09 07:34	2017-Mar-09 07:34
22	Jun 4, 2017 12:16 PM Mars enters Cancer	2017-Jun-04 12:16	2017-Jun-04 12:16
23	Sep 5, 2017 5:35 AM Mars enters Virgo	2017-Sep-05 05:35	2017-Sep-05 05:35
24	Dec 9, 2017 3:59 AM Mars enters Scorpio	2017-Dec-09 03:59	2017-Dec-09 03:59
25	Jul 1, 2019 7:19 PM Mars enters Leo	2019-Jul-01 07:19	2019-Jul-01 07:19
26	Oct 4, 2019 12:22 AM Mars enters Libra	2019-Oct-04 00:22	2019-Oct-04 00:22
27	Jan 3, 2020 4:37 AM Mars enters Sagittarius	2020-Jan-03 04:37	2020-Jan-03 04:37
28	Jan 6, 2021 5:27 PM Mars enters Taurus	2021-Jan-06 05:27	2021-Jan-06 05:27
29	Mar 3, 2021 10:29 PM Mars enters Gemini	2021-Mar-03 22:29	2021-Mar-03 22:29
30	Mar 6, 2022 1:23 AM Mars enters Aquarius	2022-Mar-06 01:23	2022-Mar-06 01:23
31	Jul 5, 2022 2:04 AM Mars enters Taurus	2022-Jul-05 02:04	2022-Jul-05 02:04
32	Jan 4, 2024 9:58 AM Mars enters Capricorn	2024-Jan-04 09:58	2024-Jan-04 09:58
33	Jun 9, 2024 12:35 AM Mars enters Taurus	2024-Jun-09 00:35	2024-Jun-09 00:35
34	Sep 4, 2024 3:46 PM Mars enters Cancer	2024-Sep-04 03:46	2024-Sep-04 03:46
35	Nov 3, 2024 11:09 PM Mars enters Leo	2024-Nov-03 23:09	2024-Nov-03 23:09
36	Jan 6, 2025 5:44 AM Mars Rx enters Cancer	2025-Jan-06 05:44	2025-Jan-06 05:44
37	Aug 6, 2025 7:23 PM Mars enters Libra	2025-Aug-06 19:23	2025-Aug-06 19:23
38	Nov 4, 2025 8:01 AM Mars enters Sagittarius	2025-Nov-04 08:01	2025-Nov-04 08:01
39
3dd

Cell Formulas
Range	Formula
B2:B38	B2	=ConvertToDate(TEXTBEFORE(A2," ",5))
C2:C38	C2	=ConvertToDate(LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",5))))