extract date from string

doriannjeshi · Nov 20, 2022

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 · Nov 21, 2022

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

Fluff · Nov 21, 2022

Glad we could help & thanks for the feedback.

Peter_SSs · Nov 21, 2022

Fluff said:
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 · Nov 21, 2022

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

VBA Code:

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