Find and Extracting Specific Data

red_cell

New Member
Joined
Jun 21, 2019
Messages
3
Hello!

I need help with a project in Excel. I have multiple cells with large amounts of text and numbers. I need to extract the listed identified time and the verified time and then calculate the turnaround time.

Here's an example of the data in the cell:

EXAM: CT Head w/o Contrast
CLINICAL INDICATION: Stroke Protocol;Other.
TECNIQUE: Helical CT images from skull base to vertex without IV contrast.
ESRC.1.1.2
Critical findings were identified on 5/29/2019 7:11 AM, read back and verified with Dr. Smith on 5/29/2019 7:16 AM by Dr. Lu, MD.

The items that are bolded and underlined are what need to be found and extracted. The format of the identified and verified times will always be the same.

Step by step instructions are most helpful so I can teach others!
Thank you in advance!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Post a link to shared excel file with representative, desensitized source data and expected result. Use GoogleDrive, OneDrive or any similar
example should reflect structure and type of the data
 
Last edited:
Upvote 0
Hello red, I have a question. Do all of your cells follow the same format where the time is listed twice in each? Or does it vary? As is, I am working on a solution that identifies the time twice in each cell using the SEARCH formula, which can identify a time pattern with the wildcards ??:?? AM or ??:?? PM. That would be followed by use of a trimming formula such as MID, which would use the number result from the SEARCH formula to determine where to begin the trim & where to end it. I have to work out a few small conditionals to get it exact.
 
Last edited:
Upvote 0
Hi Gravanoc, thanks for the reply. I believe the cells follow the same format but I've just posted a link so you can check it out. Your brief description sounds good and step by step would be greatly appreciated :)
 
Upvote 0
there is some errors like: Critical findings were identified on 6/4/2019 1228 PM, read back and verified

it will be helpful for you?

Procedure DescriptionMinMax
CT Head w/o Contrast
01/05/2019 07:55​
20/06/2019 12:24​
XR Chest 2 Views PA + Lat Stnd Protocol
01/05/2019 10:11​
17/06/2019 14:13​
US Gallbladder
06/05/2019 12:54​
06/05/2019 13:04​
XR Chest 1 View Portable
09/05/2019 07:28​
20/06/2019 11:32​
XR Abdomen 1 View (KUB)
07/05/2019 04:20​
07/06/2019 19:36​
US Head Infant
17/06/2019 16:04​
17/06/2019 16:12​
NM Lung Perf
09/05/2019 06:36​
22/05/2019 17:25​
NM GI Bleeding
06/05/2019 11:51​
13/06/2019 14:06​
XR Hip 2-3 Views Left w or w/o Pelvis
13/05/2019 14:17​
13/05/2019 14:19​
CT Cal Score Chest Read
13/05/2019 15:08​
13/05/2019 16:10​
US LE Venous Doppler Left
01/05/2019 19:19​
17/06/2019 09:46​
XR Thoracoabdomen High KUB
25/05/2019 11:47​
04/06/2019 21:11​
US LE Venous Doppler Right
03/05/2019 08:43​
10/06/2019 13:00​
XR Knee 3 Views Right Standard Protocol
31/05/2019 14:06​
31/05/2019 14:09​
XR Cardiothoracic Outside Image Consult
04/06/2019 13:16​
04/06/2019 13:23​
MRI Brain w/+ w/o Contrast
09/01/2018 00:00​
16/06/2019 00:00​
NM Hepatobiliary Scan w/Pharm
09/05/2019 16:45​
11/06/2019 17:04​
XR Gastrografin Swallow
01/05/2019 09:34​
07/06/2019 17:24​
US Pelvis Trans******l OB
01/05/2019 15:46​
16/06/2019 06:19​
US Scrotum Doppler Complete
02/05/2019 16:44​
27/05/2019 18:24​
US Scrotum
02/05/2019 16:44​
27/05/2019 18:24​
US UE Venous Doppler Left
03/05/2019 12:53​
28/05/2019 23:00​
US Pregnancy 1st Trimester Single
04/05/2019 23:17​
21/05/2019 16:43​
CT Abdomen + Pelvis w/ IV Contrast
01/05/2019 16:57​
20/06/2019 06:30​
CT Chest w/o Contrast
12/06/2018 00:00​
19/06/2019 14:23​
CT Abdomen + Pelvis w/o IV Contrast
29/04/2019 00:00​
12/06/2019 23:42​
XR Spine Lumbar 2-3 Views Stnd Protocol
15/05/2019 18:44​
15/05/2019 18:52​
US Pelvis Complete Transabdominal
02/05/2019 19:30​
16/05/2019 12:03​
US Hemodialysis Access Doppler Scan
16/05/2019 12:42​
16/05/2019 12:48​
US UE Venous Doppler Right
16/05/2019 17:02​
09/06/2019 12:33​
CT Abdomen w/o IV Contrast
17/05/2019 13:20​
17/05/2019 17:22​
US LE Venous Doppler Bilateral
08/05/2019 00:00​
14/06/2019 14:25​
XR Abdomen Acute w/ Chest 1 View
20/05/2019 13:47​
20/05/2019 13:47​
US Pelvis Limited (Non-OB)
21/05/2019 17:08​
21/05/2019 17:14​
CTA Neck w/ Contrast
05/03/2019 00:00​
19/06/2019 10:34​
CTA Head w/ Contrast
01/05/2019 08:03​
18/06/2019 00:00​
NM Hepatobiliary Scan w/o Pharm
11/06/2019 15:26​
11/06/2019 15:34​
NM Brain Scan SPECT
11/06/2019 15:25​
11/06/2019 15:25​
US UE Venous Doppler Bilateral
22/05/2019 00:00​
15/06/2019 16:39​
MRA Head w + w/o Contrast
01/05/2019 22:47​
11/06/2019 15:12​
MRA Neck w/ + w/o Contrast
01/05/2019 22:47​
11/06/2019 15:12​
MRI Spine Thoracic w/o Contrast
17/05/2019 23:05​
14/06/2019 00:00​
CT Chest w/ Contrast
01/05/2019 00:00​
19/06/2019 20:50​
CT Orbit w/o Contrast
01/05/2019 12:59​
01/05/2019 14:27​
CTA Abdomen + Pelvis w/Contrast
23/11/2016 00:00​
18/06/2019 17:23​
CTA Chest w/ + w/o Contrast
23/11/2016 00:00​
18/06/2019 17:23​
US Pelvis Trans******l Non-OB
02/05/2019 19:30​
02/05/2019 20:16​
US Pelvis Doppler Complete
02/05/2019 19:30​
02/05/2019 20:16​
NM Lung Vent/Perf Imaging
03/05/2019 12:51​
20/06/2019 11:41​
US Abdomen Limited
04/05/2019 07:07​
04/05/2019 10:03​
US Retroperitoneum Complete
04/05/2019 16:43​
04/05/2019 16:51​
MRI Brain w/o Contrast
28/05/2018 00:00​
19/06/2019 14:51​
CT Neck Soft Tissue w/ Contrast
02/05/2019 12:47​
19/06/2019 11:28​
XR Spine Cervical 2-3 Vws Stnd Protocol
15/05/2019 12:00​
15/05/2019 12:29​
CT Face w/ Contrast
03/05/2019 19:39​
27/05/2019 21:51​
PET CT Cardiac Rest/Stress
29/05/2019 13:34​
12/06/2019 12:36​
PET CT Chest Read
29/05/2019 13:34​
29/05/2019 13:34​
CT Chest w/Contrast (PE Protocol)
29/05/2019 15:50​
07/06/2019 23:57​
MRI Orbits w/o Cont
31/05/2019 12:27​
31/05/2019 12:43​
XR Barium Swallow (Esophagram)
07/06/2019 14:45​
07/06/2019 15:12​
CT Face w/o Contrast
04/05/2019 05:39​
08/06/2019 11:58​
NC Myocard Perf Rest +Stress SPECT Multi
05/06/2019 15:49​
12/06/2019 12:26​
NM Brain / BTO
11/06/2019 00:00​
12/06/2019 12:25​
CT Abdomen + Pelvis w/ + w/o IV Contrast
19/05/2019 12:22​
30/05/2019 00:00​
MRI Spine Thoracic w/+ w/o Contrast
21/07/2017 00:00​
11/06/2019 10:50​
MRI Spine Lumbar w/+ w/o Contrast
21/07/2017 00:00​
17/06/2019 14:55​
CT Head Perfusion
02/05/2019 23:50​
18/06/2019 00:00​
CTA Upper Ext w/ Contrast Right
03/05/2019 14:24​
03/05/2019 14:40​
MRI Spine Lumbar w/o Contrast
08/05/2019 13:28​
14/06/2019 00:00​
CT Head w/ + w/o Contrast
09/05/2019 10:35​
13/05/2019 14:50​
CTA Abdomen + Pelvis w/ + w/o Contrast
21/05/2019 17:44​
21/05/2019 18:18​
CT Abdominal Outside Image Consult
23/05/2019 15:13​
23/05/2019 15:38​
MRA Head w/o Contrast
28/05/2018 00:00​
13/06/2019 17:57​
CT Femur w/ Contrast Left
29/05/2019 21:00​
29/05/2019 21:02​
MRI Spine Cervical w/o Contrast
10/05/2019 00:00​
14/06/2019 00:00​
NC Chest Read
05/06/2019 15:49​
05/06/2019 15:56​
CT Spine Cervical w/o Contrast
04/05/2019 05:39​
18/06/2019 10:05​
MRI Orbits w/ + w/o Cont
19/05/2019 00:00​
16/06/2019 00:00​
MRI Brain Stealth w/o Contrast
14/06/2019 16:42​
14/06/2019 17:07​
MRI Knee w/o Contrast Left
17/06/2019 00:00​
17/06/2019 00:00​
CT Chest Tube Insert
18/06/2019 00:00​
18/06/2019 14:23​
CTA Chest w/ + w/o Contrast (Aorta)
19/06/2019 17:25​
19/06/2019 17:30​
CT Chest w/ + w/o Contrast
19/06/2019 19:52​
19/06/2019 19:52​
MRI Brain Stealth w + w/o Contrast
02/05/2019 09:56​
03/05/2019 09:18​
MRI Spine Cervical w/+ w/o Contrast
21/07/2017 00:00​
01/06/2019 21:05​
CTA Head w/ + w/o Contrast
19/01/2019 00:00​
14/06/2019 15:56​
CT Spine Lumbar w/o Contrast
13/05/2019 08:55​
13/05/2019 14:01​
CTA Abdomen+Pelvis w/+w/o Cont Abd Intrp
23/05/2019 17:49​
02/06/2019 15:03​
CT Abdomen w/ IV Contrast
24/05/2019 11:57​
24/05/2019 12:25​
CTA Abd Aorta w/ Bil Runoff
24/05/2019 23:56​
05/06/2019 18:45​
MRI Abdomen w/ + w/o Contrast
31/05/2019 08:49​
05/06/2019 16:00​
MRI Pelvis (GI/GU) w/ + w/o Contrast
05/06/2019 16:00​
05/06/2019 16:00​
MRA Neck w/o Contrast
01/05/2019 19:40​
13/06/2019 17:57​
CT Neck Soft Tissue w/ + w/o Contrast
06/06/2019 00:00​
06/06/2019 00:00​
CTA Lower Ext w/ Contrast Left
16/06/2019 00:53​
16/06/2019 01:20​
MRI Brain w/+ w/o Contrast + Perfusion
05/04/2019 00:00​
24/05/2019 14:59​
CTA Neck w/ + w/o Contrast
30/05/2019 00:00​
31/05/2019 13:38​
XR Spine Myelogram Cerv +Thor + Lumb
29/03/2019 00:00​
03/05/2019 15:22​
CT Spine Lumbar w/ Contrast
29/03/2019 00:00​
03/05/2019 15:22​
CT Spine Thoracic w/ Contrast
29/03/2019 00:00​
03/05/2019 15:22​
CT Spine Cervical w/ Contrast
29/03/2019 00:00​
03/05/2019 15:22​
MRI Neck w/ + w/o Cont
12/05/2019 13:43​
12/05/2019 14:33​
MRV Brain w/ + w/o Contrast
19/05/2019 00:00​
24/05/2019 10:11​
CTA Chest w/ Contrast
04/06/2019 13:15​
04/06/2019 16:45​
 
Last edited:
Upvote 0
I'm still working on it. The problem is that some of the cells do not follow the same format, making it difficult to use one or two search strings to match by. I have 90% success with it, but will need to review the reasons in each case for not matching. For example, some times do not have the colon ":" in the middle.
 
Upvote 0
https://drive.google.com/open?id=1JhfXox61d8XdU8L3kks98AzWXfvI_Qql

Unfortunately, after looking at several of the cells, there are a lot of instances where it was transcribed without fitting into the main pattern. These are easily fixed though, but you'll need to go through all the cells where the data says #VALUE & correct the times so that they line up with the pattern. The pattern starts at the last slash in the date, and extends to the end of the time as either AM or PM. So, 12/10/2019 1:00 PM or 12/10/2019 10:00 PM is the way those cells need to be formatted. Also, some of them did not have two times. The above link is to the workbook, it has two sheets. The second sheet is converted to values & has been sorted so that the errors are at the bottom to give you an idea of what it looks like.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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
Back
Top