extract date from string

doriannjeshi

Active Member
Joined
Apr 5, 2015
Messages
338
Office Version
  1. 365
Platform
  1. Windows
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
 
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?
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Just now tested with every single table output is 100% correct.
Really??
Fluff.xlsm
ABC
1Apr 2, 2011 12:51 AM Mars enters Aries#VALUE!Apr 2,
2Aug 3, 2011 5:22 AM Mars enters Cancer#VALUE!Aug 3,
3July 3, 2012 8:32 AM Mars enters Libra#VALUE!July 3,
4Oct 6, 2012 11:21 PM Mars enters Sagittarius#VALUE!Oct 6,
5Feb 1, 2013 8:54 PM Mars enters Pisces#VALUE!Feb 1,
6Dec 7, 2013 3:41 PM Mars enters Libra#VALUE!Dec 7,
7Dec 4, 2014 6:57 PM Mars enters Aquarius#VALUE!Dec 4,
8Jan 12, 2015 5:20 AM Mars enters Pisces#VALUE!Jan 12, 2015
9Feb 19, 2015 7:11 PM Mars enters Aries#VALUE!Feb 19, 2015
10Mar 31, 2015 12:26 PM Mars enters Taurus#VALUE!Mar 31, 2015
11May 11, 2015 10:40 PM Mars enters Gemini#VALUE!May 11, 2015
12Jun 24, 2015 9:33 AM Mars enters Cancer#VALUE!Jun 24, 2015
13Aug 8, 2015 7:32 PM Mars enters Leo#VALUE!Aug 8,
14Sep 24, 2015 10:18 PM Mars enters Virgo#VALUE!Sep 24, 2015
15Nov 12, 2015 4:41 PM Mars enters Libra#VALUE!Nov 12, 2015
16Jan 3, 2016 9:32 AM Mars enters Scorpio#VALUE!Jan 3,
17Mar 5, 2016 9:29 PM Mars enters Sagittarius#VALUE!Mar 5,
18Aug 2, 2016 1:49 PM Mars enters Sagittarius#VALUE!Aug 2,
19Nov 9, 2016 12:51 AM Mars enters Aquarius#VALUE!Nov 9,
20Mar 9, 2017 7:34 PM Mars enters Taurus#VALUE!Mar 9,
21Jun 4, 2017 12:16 PM Mars enters Cancer#VALUE!Jun 4,
22Sep 5, 2017 5:35 AM Mars enters Virgo#VALUE!Sep 5,
23Dec 9, 2017 3:59 AM Mars enters Scorpio#VALUE!Dec 9,
24Jul 1, 2019 7:19 PM Mars enters Leo#VALUE!Jul 1,
25Oct 4, 2019 12:22 AM Mars enters Libra#VALUE!Oct 4,
26Jan 3, 2020 4:37 AM Mars enters Sagittarius#VALUE!Jan 3,
27Jan 6, 2021 5:27 PM Mars enters Taurus#VALUE!Jan 6,
28Mar 3, 2021 10:29 PM Mars enters Gemini#VALUE!Mar 3,
29Mar 6, 2022 1:23 AM Mars enters Aquarius#VALUE!Mar 6,
30Jul 5, 2022 2:04 AM Mars enters Taurus#VALUE!Jul 5,
31Jan 4, 2024 9:58 AM Mars enters Capricorn#VALUE!Jan 4,
32Jun 9, 2024 12:35 AM Mars enters Taurus#VALUE!Jun 9,
33Sep 4, 2024 3:46 PM Mars enters Cancer#VALUE!Sep 4,
34Nov 3, 2024 11:09 PM Mars enters Leo#VALUE!Nov 3,
35Jan 6, 2025 5:44 AM Mars Rx enters Cancer#VALUE!Jan 6,
36Aug 6, 2025 7:23 PM Mars enters Libra#VALUE!Aug 6,
37Nov 4, 2025 8:01 AM Mars enters Sagittarius#VALUE!Nov 4,
Data
Cell Formulas
RangeFormula
B1:B37B1=VALUE(TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,"")))
C1:C37C1=TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,""))
 
Upvote 0
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
ABC
1Feb 6, 1930 1:21 PM Mars enters Aquarius06-Feb-301:21 PM
2Mar 17, 1930 12:55 AM Mars enters Pisces17-Mar-3012:55 AM
3Apr 24, 1930 12:27 PM Mars enters Aries24-Apr-3012:27 PM
4June 2, 1930 10:15 PM Mars enters Taurus02-Jun-3010:15 PM
5July 14, 1930 7:54 AM Mars enters Gemini14-Jul-307:54 AM
6Aug 28, 1930 6:27 AM Mars enters Cancer28-Aug-306:27 AM
7Apr 2, 2011 12:51 AM Mars enters Aries02-Apr-1112:51 AM
8Aug 3, 2011 5:22 AM Mars enters Cancer03-Aug-115:22 AM
9July 3, 2012 8:32 AM Mars enters Libra03-Jul-128:32 AM
10Oct 6, 2012 11:21 PM Mars enters Sagittarius06-Oct-1211:21 PM
11Feb 1, 2013 8:54 PM Mars enters Pisces01-Feb-138:54 PM
12Dec 7, 2013 3:41 PM Mars enters Libra07-Dec-133:41 PM
13Dec 4, 2014 6:57 PM Mars enters Aquarius04-Dec-146:57 PM
14Jan 12, 2015 5:20 AM Mars enters Pisces12-Jan-155:20 AM
15Feb 19, 2015 7:11 PM Mars enters Aries19-Feb-157:11 PM
16Mar 31, 2015 12:26 PM Mars enters Taurus31-Mar-1512:26 PM
17May 11, 2015 10:40 PM Mars enters Gemini11-May-1510:40 PM
18Jun 24, 2015 9:33 AM Mars enters Cancer24-Jun-159:33 AM
19Aug 8, 2015 7:32 PM Mars enters Leo08-Aug-157:32 PM
20Sep 24, 2015 10:18 PM Mars enters Virgo24-Sep-1510:18 PM
21Nov 12, 2015 4:41 PM Mars enters Libra12-Nov-154:41 PM
22Jan 3, 2016 9:32 AM Mars enters Scorpio03-Jan-169:32 AM
23Mar 5, 2016 9:29 PM Mars enters Sagittarius05-Mar-169:29 PM
24Aug 2, 2016 1:49 PM Mars enters Sagittarius02-Aug-161:49 PM
25Nov 9, 2016 12:51 AM Mars enters Aquarius09-Nov-1612:51 AM
26Mar 9, 2017 7:34 PM Mars enters Taurus09-Mar-177:34 PM
27Jun 4, 2017 12:16 PM Mars enters Cancer04-Jun-1712:16 PM
28Sep 5, 2017 5:35 AM Mars enters Virgo05-Sep-175:35 AM
29Dec 9, 2017 3:59 AM Mars enters Scorpio09-Dec-173:59 AM
30Jul 1, 2019 7:19 PM Mars enters Leo01-Jul-197:19 PM
31Oct 4, 2019 12:22 AM Mars enters Libra04-Oct-1912:22 AM
32Jan 3, 2020 4:37 AM Mars enters Sagittarius03-Jan-204:37 AM
33Jan 6, 2021 5:27 PM Mars enters Taurus06-Jan-215:27 PM
34Mar 3, 2021 10:29 PM Mars enters Gemini03-Mar-2110:29 PM
35Mar 6, 2022 1:23 AM Mars enters Aquarius06-Mar-221:23 AM
36Jul 5, 2022 2:04 AM Mars enters Taurus05-Jul-222:04 AM
37Jan 4, 2024 9:58 AM Mars enters Capricorn04-Jan-249:58 AM
38Jun 9, 2024 12:35 AM Mars enters Taurus09-Jun-2412:35 AM
39Sep 4, 2024 3:46 PM Mars enters Cancer04-Sep-243:46 PM
40Nov 3, 2024 11:09 PM Mars enters Leo03-Nov-2411:09 PM
41Jan 6, 2025 5:44 AM Mars Rx enters Cancer06-Jan-255:44 AM
42Aug 6, 2025 7:23 PM Mars enters Libra06-Aug-257:23 PM
43Nov 4, 2025 8:01 AM Mars enters Sagittarius04-Nov-258:01 AM
Dates Times
Cell Formulas
RangeFormula
B1:B43B1=DATEVALUE(MID(A1,FIND(",",A1)-2,2)&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))
C1:C43C1=TIMEVALUE(MID(A1,FIND(":",A1)-2,8))
No these dates are not solved on my version , I don't know the reason!
 
Upvote 0
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
AB
1Feb 6, 1930 1:21 PM Mars enters Aquarius10995
2Apr 2, 2011 12:51 AM Mars enters Aries#VALUE!
3Aug 3, 2011 5:22 AM Mars enters Cancer#VALUE!
4July 3, 2012 8:32 AM Mars enters Libra#VALUE!
5Oct 6, 2012 11:21 PM Mars enters Sagittarius#VALUE!
6Feb 1, 2013 8:54 PM Mars enters Pisces#VALUE!
7Dec 7, 2013 3:41 PM Mars enters Libra#VALUE!
8Dec 4, 2014 6:57 PM Mars enters Aquarius#VALUE!
9Jan 12, 2015 5:20 AM Mars enters Pisces42016
10Feb 19, 2015 7:11 PM Mars enters Aries42054
11Mar 31, 2015 12:26 PM Mars enters Taurus42094
12May 11, 2015 10:40 PM Mars enters Gemini42135
13Jun 24, 2015 9:33 AM Mars enters Cancer42179
14Aug 8, 2015 7:32 PM Mars enters Leo#VALUE!
15Sep 24, 2015 10:18 PM Mars enters Virgo42271
16Nov 12, 2015 4:41 PM Mars enters Libra42320
17Jan 3, 2016 9:32 AM Mars enters Scorpio#VALUE!
18Mar 5, 2016 9:29 PM Mars enters Sagittarius#VALUE!
19Aug 2, 2016 1:49 PM Mars enters Sagittarius#VALUE!
20Nov 9, 2016 12:51 AM Mars enters Aquarius#VALUE!
21Mar 9, 2017 7:34 PM Mars enters Taurus#VALUE!
22Jun 4, 2017 12:16 PM Mars enters Cancer#VALUE!
23Sep 5, 2017 5:35 AM Mars enters Virgo#VALUE!
24Dec 9, 2017 3:59 AM Mars enters Scorpio#VALUE!
25Jul 1, 2019 7:19 PM Mars enters Leo#VALUE!
26Oct 4, 2019 12:22 AM Mars enters Libra#VALUE!
27Jan 3, 2020 4:37 AM Mars enters Sagittarius#VALUE!
28Jan 6, 2021 5:27 PM Mars enters Taurus#VALUE!
29Mar 3, 2021 10:29 PM Mars enters Gemini#VALUE!
30Mar 6, 2022 1:23 AM Mars enters Aquarius#VALUE!
31Jul 5, 2022 2:04 AM Mars enters Taurus#VALUE!
32Jan 4, 2024 9:58 AM Mars enters Capricorn#VALUE!
33Jun 9, 2024 12:35 AM Mars enters Taurus#VALUE!
34Sep 4, 2024 3:46 PM Mars enters Cancer#VALUE!
35Nov 3, 2024 11:09 PM Mars enters Leo#VALUE!
36Jan 6, 2025 5:44 AM Mars Rx enters Cancer#VALUE!
37Aug 6, 2025 7:23 PM Mars enters Libra#VALUE!
38Nov 4, 2025 8:01 AM Mars enters Sagittarius#VALUE!
Sheet4
Cell Formulas
RangeFormula
B1:B38B1=DATEVALUE(MID(A1,FIND(",",A1)-2,2)&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))
 
Upvote 0
If you put this formula in B2 & drag across, what do you get
Fluff.xlsm
ABCDEFGH
1Feb 6, 1930 1:21 PM Mars enters Aquarius
2Apr 2, 2011 12:51 AM Mars enters Aries6511211432325044
Data
Cell Formulas
RangeFormula
B2:H2B2=CODE(MID($A2,COLUMNS($B2:B2),1))
 
Upvote 0
Really??
Fluff.xlsm
ABC
1Apr 2, 2011 12:51 AM Mars enters Aries#VALUE!Apr 2,
2Aug 3, 2011 5:22 AM Mars enters Cancer#VALUE!Aug 3,
3July 3, 2012 8:32 AM Mars enters Libra#VALUE!July 3,
4Oct 6, 2012 11:21 PM Mars enters Sagittarius#VALUE!Oct 6,
5Feb 1, 2013 8:54 PM Mars enters Pisces#VALUE!Feb 1,
6Dec 7, 2013 3:41 PM Mars enters Libra#VALUE!Dec 7,
7Dec 4, 2014 6:57 PM Mars enters Aquarius#VALUE!Dec 4,
8Jan 12, 2015 5:20 AM Mars enters Pisces#VALUE!Jan 12, 2015
9Feb 19, 2015 7:11 PM Mars enters Aries#VALUE!Feb 19, 2015
10Mar 31, 2015 12:26 PM Mars enters Taurus#VALUE!Mar 31, 2015
11May 11, 2015 10:40 PM Mars enters Gemini#VALUE!May 11, 2015
12Jun 24, 2015 9:33 AM Mars enters Cancer#VALUE!Jun 24, 2015
13Aug 8, 2015 7:32 PM Mars enters Leo#VALUE!Aug 8,
14Sep 24, 2015 10:18 PM Mars enters Virgo#VALUE!Sep 24, 2015
15Nov 12, 2015 4:41 PM Mars enters Libra#VALUE!Nov 12, 2015
16Jan 3, 2016 9:32 AM Mars enters Scorpio#VALUE!Jan 3,
17Mar 5, 2016 9:29 PM Mars enters Sagittarius#VALUE!Mar 5,
18Aug 2, 2016 1:49 PM Mars enters Sagittarius#VALUE!Aug 2,
19Nov 9, 2016 12:51 AM Mars enters Aquarius#VALUE!Nov 9,
20Mar 9, 2017 7:34 PM Mars enters Taurus#VALUE!Mar 9,
21Jun 4, 2017 12:16 PM Mars enters Cancer#VALUE!Jun 4,
22Sep 5, 2017 5:35 AM Mars enters Virgo#VALUE!Sep 5,
23Dec 9, 2017 3:59 AM Mars enters Scorpio#VALUE!Dec 9,
24Jul 1, 2019 7:19 PM Mars enters Leo#VALUE!Jul 1,
25Oct 4, 2019 12:22 AM Mars enters Libra#VALUE!Oct 4,
26Jan 3, 2020 4:37 AM Mars enters Sagittarius#VALUE!Jan 3,
27Jan 6, 2021 5:27 PM Mars enters Taurus#VALUE!Jan 6,
28Mar 3, 2021 10:29 PM Mars enters Gemini#VALUE!Mar 3,
29Mar 6, 2022 1:23 AM Mars enters Aquarius#VALUE!Mar 6,
30Jul 5, 2022 2:04 AM Mars enters Taurus#VALUE!Jul 5,
31Jan 4, 2024 9:58 AM Mars enters Capricorn#VALUE!Jan 4,
32Jun 9, 2024 12:35 AM Mars enters Taurus#VALUE!Jun 9,
33Sep 4, 2024 3:46 PM Mars enters Cancer#VALUE!Sep 4,
34Nov 3, 2024 11:09 PM Mars enters Leo#VALUE!Nov 3,
35Jan 6, 2025 5:44 AM Mars Rx enters Cancer#VALUE!Jan 6,
36Aug 6, 2025 7:23 PM Mars enters Libra#VALUE!Aug 6,
37Nov 4, 2025 8:01 AM Mars enters Sagittarius#VALUE!Nov 4,
Data
Cell Formulas
RangeFormula
B1:B37B1=VALUE(TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,"")))
C1:C37C1=TRIM(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),95,""))
 

Attachments

  • Capture.PNG
    Capture.PNG
    87.8 KB · Views: 11
Upvote 0
The formula you show there is nothing like the one you posted & said you had tested with 100% success.
 
Upvote 0
If you put this formula in B2 & drag across, what do you get
Fluff.xlsm
ABCDEFGH
1Feb 6, 1930 1:21 PM Mars enters Aquarius
2Apr 2, 2011 12:51 AM Mars enters Aries6511211432325044
Data
Cell Formulas
RangeFormula
B2:H2B2=CODE(MID($A2,COLUMNS($B2:B2),1))
pyth new.xlsb
ABCDEFGH
1Feb 6, 1930 1:21 PM Mars enters Aquarius
2Apr 2, 2011 12:51 AM Mars enters Aries65112114321605044
Sheet5
Cell Formulas
RangeFormula
B2:H2B2=CODE(MID($A2,COLUMNS($B2:B2),1))
 
Upvote 0
Thanks for that.
How about this tweak to Peter's formula
Excel Formula:
=DATEVALUE(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND(",",A1)-2,2)&LEFT(SUBSTITUTE(A1,CHAR(160)," "),FIND(" ",A1))&MID(A1,FIND(",",A1)+2,4))
 
Upvote 0
Solution

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top