Get the first and last occurrence of a timestamp from a cell

[owlf]

I have an Excel file that contains the chatbot log from a website. Instead a cell in each row, the full chat conversation is saved. What I am looking to do is extract the first timestamp that appears as well as the last one. Each chatbot conversation can have different amounts of timestamps in the cell. Sometimes a few and at other times 20+. I am only interested in the first and last occurrence.

Ideally, I'd like a formula to get this info and not VBA.

There can be two formulas if needed to get the first and last timestamps into their own cells. In the chart below, the first column is the chat conversation and the 2nd and 3rd column is where I would like to place the timestamps. Let me know if you have any questions. Thanks in advance!

ChatConversation	First Timestamp	Last Timestamp
03/11/2020 10:45:20 PM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/11/2020 10:45:24 PM - USER Hi, can you please help me? 03/11/2020 10:45:26 PM - CHATBOT Please type out your full first and last name without the use of initials. Can I ask for your name? 03/11/2020 10:45:40 PM - USER John Doe 03/11/2020 10:45:41 PM - CHATBOT Thanks, John Doe! Can I also get your email in case we get disconnected? 03/11/2020 10:46:24 PM - USER test@test.com 03/11/2020 10:46:26 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/11/2020 10:46:35 PM - USER Yes 03/11/2020 10:46:37 PM - CHATBOT Excellent, let’s schedule a meeting! I just need to collect a little information about your company first. What’s the name of your company? 03/11/2020 10:46:45 PM - USER ABC 03/11/2020 10:46:46 PM - CHATBOT Got it! How many employees do you have at your company? 03/11/2020 10:46:56 PM - USER 100+ 03/11/2020 10:46:58 PM - CHATBOT Thanks for sharing! What aspects of our services prompted your interest in us?	10:45:20 PM	10:46:58 PM

 

[sandy666]

you are welcome
if it works for you hit Like in the post with solution