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

 

[rlv01]

One way.

Book2
	A	B	C
1	Feb 6, 1930 1:21 PM Mars enters Aquarius	06/02/1930	1:21 PM
2	Mar 17, 1930 12:55 AM Mars enters Pisces	17/03/1930	12:55 AM
3	Apr 24, 1930 12:27 PM Mars enters Aries	24/04/1930	12:27 PM
4	June 2, 1930 10:15 PM Mars enters Taurus	02/06/1930	10:15 PM
5	July 14, 1930 7:54 AM Mars enters Gemini	14/07/1930	7:54 AM
6	Aug 28, 1930 6:27 AM Mars enters Cancer	28/08/1930	6:27 AM
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=DATEVALUE(LEFT(A1,FIND("!",SUBSTITUTE(A1," ","!",3))))
C1:C6	C1	=TIMEVALUE(MID(A1,FIND("!",SUBSTITUTE(A1," ","!",3)),9))

 

[doriannjeshi]

It's working except for a dozen of values like these ones:

Apr 2, 2011 12:51 AM Mars enters Aries

Aug 3, 2011 5:22 AM Mars enters Cancer

July 3, 2012 8:32 AM Mars enters Libra

Oct 6, 2012 11:21 PM Mars enters Sagittarius

Feb 1, 2013 8:54 PM Mars enters Pisces

Dec 7, 2013 3:41 PM Mars enters Libra

Dec 4, 2014 6:57 PM Mars enters Aquarius

Jan 12, 2015 5:20 AM Mars enters Pisces

Feb 19, 2015 7:11 PM Mars enters Aries

Mar 31, 2015 12:26 PM Mars enters Taurus

May 11, 2015 10:40 PM Mars enters Gemini

 

[rlv01]

It's working except for a dozen of values like these ones:

I don't understand what you mean when you say it is not working.
When I try your new values using the same formulas, they seem to get converted properly.

Book2
	A	B	C
1	Feb 6, 1930 1:21 PM Mars enters Aquarius	06/02/1930	1:21 PM
2	Mar 17, 1930 12:55 AM Mars enters Pisces	17/03/1930	12:55 AM
3	Apr 24, 1930 12:27 PM Mars enters Aries	24/04/1930	12:27 PM
4	June 2, 1930 10:15 PM Mars enters Taurus	02/06/1930	10:15 PM
5	July 14, 1930 7:54 AM Mars enters Gemini	14/07/1930	7:54 AM
6	Aug 28, 1930 6:27 AM Mars enters Cancer	28/08/1930	6:27 AM
7	Apr 2, 2011 12:51 AM Mars enters Aries	02/04/2011	12:51 AM
8	Aug 3, 2011 5:22 AM Mars enters Cancer	03/08/2011	5:22 AM
9	July 3, 2012 8:32 AM Mars enters Libra	03/07/2012	8:32 AM
10	Oct 6, 2012 11:21 PM Mars enters Sagittarius	06/10/2012	11:21 PM
11	Feb 1, 2013 8:54 PM Mars enters Pisces	01/02/2013	8:54 PM
12	Dec 7, 2013 3:41 PM Mars enters Libra	07/12/2013	3:41 PM
13	Dec 4, 2014 6:57 PM Mars enters Aquarius	04/12/2014	6:57 PM
14	Jan 12, 2015 5:20 AM Mars enters Pisces	12/01/2015	5:20 AM
15	Feb 19, 2015 7:11 PM Mars enters Aries	19/02/2015	7:11 PM
16	Mar 31, 2015 12:26 PM Mars enters Taurus	31/03/2015	12:26 PM
17	May 11, 2015 10:40 PM Mars enters Gemini	11/05/2015	10:40 PM
Sheet1

Cell Formulas
Range	Formula
B1:B17	B1	=DATEVALUE(LEFT(A1,FIND("!",SUBSTITUTE(A1," ","!",3))))
C1:C17	C1	=TIMEVALUE(MID(A1,FIND("!",SUBSTITUTE(A1," ","!",3)),9))

 

[rlv01]

The formula can also be 'hardened' to handle extra spaces

Book2
	A	B	C
1	Dec 4, 2014 6:57 PM Mars enters Aquarius	04/12/2014	6:57 PM
2	Jan 12, 2015 5:20 AM Mars enters Pisces	12/01/2015	5:20 AM
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=DATEVALUE(LEFT(TRIM(A1),FIND("!",SUBSTITUTE(TRIM(A1)," ","!",3))))
C1:C2	C1	=TIMEVALUE(MID(TRIM(A1),FIND("!",SUBSTITUTE(TRIM(A1)," ","!",3)),9))

 

[doriannjeshi]

these dates wont budge

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

Cell Formulas
Range	Formula
B1:B37	B1	=DATEVALUE(LEFT(A1,FIND("!",SUBSTITUTE(A1," ","!",3))))
C1:C37	C1	=DATEVALUE(LEFT(TRIM(A1),FIND("!",SUBSTITUTE(TRIM(A1)," ","!",3))))

 

[Peter_SSs]

these dates wont budge

.. yet you have marked post #2 as the solution. Can you clarify whether your question is resolved or not?

 

[doriannjeshi]

It is resolved 95% and I have to do some manual fixing, so it is the solution unless there is another formula that deals with these hard dates !

Or should I open another thread for these dates since they weren't in the first post I made?

 

[Peter_SSs]

Or should I open another thread for these dates since they weren't in the first post I made?

You should persist in this thread since it is so closely related.

Does this solve the issue?

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

Cell Formulas
Range	Formula
B1:B43	B1	=DATEVALUE(MID(A1,FIND(",",A1)-2,2)&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))
C1:C43	C1	=TIMEVALUE(MID(A1,FIND(":",A1)-2,8))

 

[shinigamilight]

enter this in B1 =VALUE(TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,"")))
enter this in C1 =VALUE(TRIM(REPLACE(MID(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95),FIND("Mars",MID(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95)),95,"")))