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)

 

[Rick Rothstein]

Give this formula a try...

=TRIM(MID(E1,FIND(",",E1)+2,LEN(E1)-FIND(",",E1)-12))

 

[tbruce]

Rick,

Thanks for the response - what I pasted into the post got translated - here is the actual cell content in Cell E1 - I'm looking to strip out the date and time:

<div><div><span Style="font-weight: bold">Enrollee:</span> SMITH</div><div><span Style="font-weight: bold">Type:</span>Point</div><div><span Style="font-weight: bold">Date:</span> Wednesday, Jun 20, 2018 9:03:26 PM</div><div><span Style="font-weight: bold">Location:</span> 33.000000, -110.0000</div><div><span Style="font-weight: bold"><span>Speed</span>:</span> 2 MPH</div><div><span Style="font-weight: bold"><span>Voltage</span>:</span> 3.96v</div><div><span Style="font-weight: bold"><span>HDOP</span>:</span> 5</div><div><span Style="font-weight: bold"><span>Satellites In View</span>:</span> 10</div><div><span Style="font-weight: bold"><span>Satellites Used</span>:</span> 8</div><div><span Style="font-weight: bold"><span>Open Events</span> </span></div> <div Style="padding-left: 20px"><span>None</span></div></div>

 

[jtakw]

Hi,

If you mean Date AND Time:

Book1
	E	F
1	Date: Wednesday, Jun 20, 2018 9:04:26 PM	Jun 20, 2018 9:04:26 PM
2
3		Custom format
4		mmm dd, yyyy h:mm:ss AM/PM
Sheet159

Cell Formulas
Range	Formula
F1	=MID(E1,FIND(",",E1)+2,255)+0

 

[Rick Rothstein]

Give this formula a try then...

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

 

[tbruce]

Didn't work - get message "The formula is missing an opening or closing parenthesis."

 

[jtakw]

If you Don't want the result converted to Real Date/Time value, then just remove the +0 at the end of my formula in Post #4 . In this case, No Custom format needed, but result is Text.

 

[tbruce]

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

 

[Rick Rothstein]

Didn't work - get message "The formula is missing an opening or closing parenthesis."

Sorry, try this one...

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

 

[tbruce]

Rick,

That worked. You rock as always. You always seem to respond with answers. Thank you so much for always being willing to help.

Thank,

Tom