Help with MID Formula

[tbruce]

HI,

I've been trying to come up with the formula to parse the date and time from this string of data in a cell, but can't seem to come up with it. Really hoping someone could help me with this formula:

Here is the data in E1 cell:

Date:</span> Wednesday, Jun 20, 2018 9:04:26 PM</div>

Here is the formula I'm using that doesn't work. Tried several combos:

=MID(E1,SEARCH("Date:</span> ",E1)+13,SEARCH("</div>",E1)-SEARCH("Date:</span> ",E1)-14)

 

[jtakw]

Sorry - your formula didn't work jtakw. See the revised text that I'm trying to parse above

Thought that was some kind of weird display error....

Try this, result converted to Real Date/Time value:

Book1
	E	F
1	Enrollee: SMITHType:PointDate: Wednesday, Jun 20, 2018 9:03:26 PMLocation: 33.000000, -110.0000Speed: 2 MPHVoltage: 3.96vHDOP: 5Satellites In View: 10Satellites Used: 8Open EventsNone	Jun 20, 2018 9:03:26 PM
2
3		Custom format
4		mmm dd, yyyy h:mm:ss AM/PM
Sheet160

Cell Formulas
Range	Formula
F1	=LEFT(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND(",",E1)+2,255),"AM","AM"&REPT(" ",100)),"PM","PM"&REPT(" ",100)),100)+0

 

[jtakw]

Sorry, try this one...

=TRIM(SUBSTITUTE(MID(E1,IFERROR(SEARCH(" AM<",E1),SEARCH(" PM<",E1))-21,24),",",""))

Hi Rick,

I think you're compensating for cases of single digit days, but the comma will be missing after the day?

 

[tbruce]

This didn't work - get the #VALUE !

 

[Rick Rothstein]

Hi Rick,

I think you're compensating for cases of single digit days, but the comma will be missing after the day?

I am not sure what point you are trying to make... if the date is such that the comma is not part of the returned value, then the SUBSTITUTE function will do nothing and just return that value to the TRIM function. What am I missing about your post?

This didn't work - get the #VALUE !

When there are multiple repliers, you should really include a reference to whom your post is directed.

 

[tbruce]

Sorry - this was was related to the other poster that questioned your formula - his didn't work

 

[jtakw]

Ok Rick, first of all, I'm not even sure Exactly what the OP's data looks like...
Is it really like my sample post #11 ? Is that what we're working with?
Because if I click "Reply With Quote" in OP's post #3 , I see something completely different.

Assuming we're working with this data, the comma is missing in the Result after the day:

Book1
	E	F	G
1	Enrollee: SMITHType:PointDate: Wednesday, Jun 20, 2018 9:03:26 PMLocation: 33.000000, -110.0000Speed: 2 MPHVoltage: 3.96vHDOP: 5Satellites In View: 10Satellites Used: 8Open EventsNone	Jun 20, 2018 9:03:26 PM	Jun 20 2018 9:03:26 PM
Sheet160

Cell Formulas
Range	Formula
F1	=LEFT(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND(",",E1)+2,255),"AM","AM"&REPT(" ",100)),"PM","PM"&REPT(" ",100)),100)+0
G1	=TRIM(SUBSTITUTE(MID(E1,IFERROR(SEARCH(" AM<",E1),SEARCH(" PM<",E1))-21,24),",",""))

 

[Rick Rothstein]

Ok Rick, first of all, I'm not even sure Exactly what the OP's data looks like...
Is it really like my sample post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=11]#11 [/URL] ? Is that what we're working with?
Because if I click "Reply With Quote" in OP's post #3 , I see something completely different.

Assuming we're working with this data, the comma is missing in the Result after the day:

When replying with a post that contains HTML tags that you wish to suppress, you need to scroll down to the bordered area labeled "Additional Options" and find the area within it marked "HTML" click it drop-down and pick the "HTML Off" option.

As for the comma after the day name... I see a comma after the word "Wednesday" in both Message #11 and (your) Message #16 . Are you saying you don't see it?

 

[jtakw]

This didn't work - get the #VALUE !

Sorry - this was was related to the other poster that questioned your formula - his didn't work

Again, assuming, that data is like the sample shown, my formula works, I told you the answer is converted to REAL Date/Time Value, you HAVE to format the cell as I instructed, otherwise you'll just see 43271.8773842593

which is the numerical value of Jun 20, 2018 9:03:26 PM

If you Don't want Real Date/Time values that you can do further Comparisons with, and prefer to have the result as Text, I did mention to take Out the +0 at the end of my formula:

Book1
	E	F	G
1	Enrollee: SMITHType:PointDate: Wednesday, Jun 20, 2018 9:03:26 PMLocation: 33.000000, -110.0000Speed: 2 MPHVoltage: 3.96vHDOP: 5Satellites In View: 10Satellites Used: 8Open EventsNone	Jun 20, 2018 9:03:26 PM	Jun 20, 2018 9:03:26 PM
2
3		Custom format	Result is TEXT
4		mmm dd, yyyy h:mm:ss AM/PM	No custom format
Sheet160

Cell Formulas
Range	Formula
F1	=LEFT(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND(",",E1)+2,255),"AM","AM"&REPT(" ",100)),"PM","PM"&REPT(" ",100)),100)+0
G1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND(",",E1)+2,255),"AM","AM"&REPT(" ",100)),"PM","PM"&REPT(" ",100)),100))

 

[jtakw]

@Rick, I see the comma after the day Jun 20, 2018 9:03:26 PM using my formula, but I don't see it with your formula Jun 20 2018 9:03:26 PM

You had Substituted it out in your formula with a ""