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

owlf

New Member
Joined
May 18, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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!

ChatConversationFirst TimestampLast 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 PM10:46:58 PM
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you are welcome
if it works for you hit Like in the post with solution
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,127,715
Messages
5,626,457
Members
416,186
Latest member
shamm28

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
Top