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

 

[Peter_SSs]

enter this in B1 =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,"")))

Have you tested those on the actual sample data provided in posts 1, 3 & 6?

 

[shinigamilight]

Have you tested those on the actual sample data provided in posts 1, 3 & 6?

Just now tested with every single table output is 100% correct.

 

[Fluff]

Just now tested with every single table output is 100% correct.

Really??

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

Cell Formulas
Range	Formula
B1:B37	B1	=VALUE(TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,"")))
C1:C37	C1	=TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,""))

 

[doriannjeshi]

I am sorry but still these dates

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))

No these dates are not solved on my version , I don't know the reason!

 

[doriannjeshi]

I am sorry but still these dates

No these dates are not solved on my version , I don't know the reason!

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

Cell Formulas
Range	Formula
B1:B38	B1	=DATEVALUE(MID(A1,FIND(",",A1)-2,2)&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))

 

[Fluff]

If you put this formula in B2 & drag across, what do you get

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Feb 6, 1930 1:21 PM Mars enters Aquarius
2	Apr 2, 2011 12:51 AM Mars enters Aries	65	112	114	32	32	50	44
Data

Cell Formulas
Range	Formula
B2:H2	B2	=CODE(MID($A2,COLUMNS($B2:B2),1))

 

[shinigamilight]

Really??

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

Cell Formulas
Range	Formula
B1:B37	B1	=VALUE(TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,"")))
C1:C37	C1	=TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,""))

 

[Fluff]

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

 

[doriannjeshi]

If you put this formula in B2 & drag across, what do you get

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Feb 6, 1930 1:21 PM Mars enters Aquarius
2	Apr 2, 2011 12:51 AM Mars enters Aries	65	112	114	32	32	50	44
Data

Cell Formulas
Range	Formula
B2:H2	B2	=CODE(MID($A2,COLUMNS($B2:B2),1))

pyth new.xlsb
	A	B	C	D	E	F	G	H
1	Feb 6, 1930 1:21 PM Mars enters Aquarius
2	Apr 2, 2011 12:51 AM Mars enters Aries	65	112	114	32	160	50	44
Sheet5

Cell Formulas
Range	Formula
B2:H2	B2	=CODE(MID($A2,COLUMNS($B2:B2),1))

 

[Fluff]

Thanks for that.
How about this tweak to Peter's formula

=DATEVALUE(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND(",",A1)-2,2)&LEFT(SUBSTITUTE(A1,CHAR(160)," "),FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))