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

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.
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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Procedure Description[/td][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Head w/o Contrast[/td][td=bgcolor:#E2EFDA]
01/05/2019 07:55​
[/td][td=bgcolor:#E2EFDA]
20/06/2019 12:24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Chest 2 Views PA + Lat Stnd Protocol[/td][td]
01/05/2019 10:11​
[/td][td]
17/06/2019 14:13​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Gallbladder[/td][td=bgcolor:#E2EFDA]
06/05/2019 12:54​
[/td][td=bgcolor:#E2EFDA]
06/05/2019 13:04​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Chest 1 View Portable[/td][td]
09/05/2019 07:28​
[/td][td]
20/06/2019 11:32​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]XR Abdomen 1 View (KUB)[/td][td=bgcolor:#E2EFDA]
07/05/2019 04:20​
[/td][td=bgcolor:#E2EFDA]
07/06/2019 19:36​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Head Infant[/td][td]
17/06/2019 16:04​
[/td][td]
17/06/2019 16:12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]NM Lung Perf[/td][td=bgcolor:#E2EFDA]
09/05/2019 06:36​
[/td][td=bgcolor:#E2EFDA]
22/05/2019 17:25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NM GI Bleeding[/td][td]
06/05/2019 11:51​
[/td][td]
13/06/2019 14:06​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]XR Hip 2-3 Views Left w or w/o Pelvis[/td][td=bgcolor:#E2EFDA]
13/05/2019 14:17​
[/td][td=bgcolor:#E2EFDA]
13/05/2019 14:19​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Cal Score Chest Read[/td][td]
13/05/2019 15:08​
[/td][td]
13/05/2019 16:10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US LE Venous Doppler Left[/td][td=bgcolor:#E2EFDA]
01/05/2019 19:19​
[/td][td=bgcolor:#E2EFDA]
17/06/2019 09:46​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Thoracoabdomen High KUB[/td][td]
25/05/2019 11:47​
[/td][td]
04/06/2019 21:11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US LE Venous Doppler Right[/td][td=bgcolor:#E2EFDA]
03/05/2019 08:43​
[/td][td=bgcolor:#E2EFDA]
10/06/2019 13:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Knee 3 Views Right Standard Protocol[/td][td]
31/05/2019 14:06​
[/td][td]
31/05/2019 14:09​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]XR Cardiothoracic Outside Image Consult[/td][td=bgcolor:#E2EFDA]
04/06/2019 13:16​
[/td][td=bgcolor:#E2EFDA]
04/06/2019 13:23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Brain w/+ w/o Contrast[/td][td]
09/01/2018 00:00​
[/td][td]
16/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]NM Hepatobiliary Scan w/Pharm[/td][td=bgcolor:#E2EFDA]
09/05/2019 16:45​
[/td][td=bgcolor:#E2EFDA]
11/06/2019 17:04​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Gastrografin Swallow[/td][td]
01/05/2019 09:34​
[/td][td]
07/06/2019 17:24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Pelvis Trans******l OB[/td][td=bgcolor:#E2EFDA]
01/05/2019 15:46​
[/td][td=bgcolor:#E2EFDA]
16/06/2019 06:19​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Scrotum Doppler Complete[/td][td]
02/05/2019 16:44​
[/td][td]
27/05/2019 18:24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Scrotum[/td][td=bgcolor:#E2EFDA]
02/05/2019 16:44​
[/td][td=bgcolor:#E2EFDA]
27/05/2019 18:24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US UE Venous Doppler Left[/td][td]
03/05/2019 12:53​
[/td][td]
28/05/2019 23:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Pregnancy 1st Trimester Single[/td][td=bgcolor:#E2EFDA]
04/05/2019 23:17​
[/td][td=bgcolor:#E2EFDA]
21/05/2019 16:43​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Abdomen + Pelvis w/ IV Contrast[/td][td]
01/05/2019 16:57​
[/td][td]
20/06/2019 06:30​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Chest w/o Contrast[/td][td=bgcolor:#E2EFDA]
12/06/2018 00:00​
[/td][td=bgcolor:#E2EFDA]
19/06/2019 14:23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Abdomen + Pelvis w/o IV Contrast[/td][td]
29/04/2019 00:00​
[/td][td]
12/06/2019 23:42​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]XR Spine Lumbar 2-3 Views Stnd Protocol[/td][td=bgcolor:#E2EFDA]
15/05/2019 18:44​
[/td][td=bgcolor:#E2EFDA]
15/05/2019 18:52​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Pelvis Complete Transabdominal[/td][td]
02/05/2019 19:30​
[/td][td]
16/05/2019 12:03​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Hemodialysis Access Doppler Scan[/td][td=bgcolor:#E2EFDA]
16/05/2019 12:42​
[/td][td=bgcolor:#E2EFDA]
16/05/2019 12:48​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US UE Venous Doppler Right[/td][td]
16/05/2019 17:02​
[/td][td]
09/06/2019 12:33​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Abdomen w/o IV Contrast[/td][td=bgcolor:#E2EFDA]
17/05/2019 13:20​
[/td][td=bgcolor:#E2EFDA]
17/05/2019 17:22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US LE Venous Doppler Bilateral[/td][td]
08/05/2019 00:00​
[/td][td]
14/06/2019 14:25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]XR Abdomen Acute w/ Chest 1 View[/td][td=bgcolor:#E2EFDA]
20/05/2019 13:47​
[/td][td=bgcolor:#E2EFDA]
20/05/2019 13:47​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Pelvis Limited (Non-OB)[/td][td]
21/05/2019 17:08​
[/td][td]
21/05/2019 17:14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CTA Neck w/ Contrast[/td][td=bgcolor:#E2EFDA]
05/03/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
19/06/2019 10:34​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Head w/ Contrast[/td][td]
01/05/2019 08:03​
[/td][td]
18/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]NM Hepatobiliary Scan w/o Pharm[/td][td=bgcolor:#E2EFDA]
11/06/2019 15:26​
[/td][td=bgcolor:#E2EFDA]
11/06/2019 15:34​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NM Brain Scan SPECT[/td][td]
11/06/2019 15:25​
[/td][td]
11/06/2019 15:25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US UE Venous Doppler Bilateral[/td][td=bgcolor:#E2EFDA]
22/05/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
15/06/2019 16:39​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRA Head w + w/o Contrast[/td][td]
01/05/2019 22:47​
[/td][td]
11/06/2019 15:12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRA Neck w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
01/05/2019 22:47​
[/td][td=bgcolor:#E2EFDA]
11/06/2019 15:12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Spine Thoracic w/o Contrast[/td][td]
17/05/2019 23:05​
[/td][td]
14/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Chest w/ Contrast[/td][td=bgcolor:#E2EFDA]
01/05/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
19/06/2019 20:50​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Orbit w/o Contrast[/td][td]
01/05/2019 12:59​
[/td][td]
01/05/2019 14:27​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CTA Abdomen + Pelvis w/Contrast[/td][td=bgcolor:#E2EFDA]
23/11/2016 00:00​
[/td][td=bgcolor:#E2EFDA]
18/06/2019 17:23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Chest w/ + w/o Contrast[/td][td]
23/11/2016 00:00​
[/td][td]
18/06/2019 17:23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Pelvis Trans******l Non-OB[/td][td=bgcolor:#E2EFDA]
02/05/2019 19:30​
[/td][td=bgcolor:#E2EFDA]
02/05/2019 20:16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Pelvis Doppler Complete[/td][td]
02/05/2019 19:30​
[/td][td]
02/05/2019 20:16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]NM Lung Vent/Perf Imaging[/td][td=bgcolor:#E2EFDA]
03/05/2019 12:51​
[/td][td=bgcolor:#E2EFDA]
20/06/2019 11:41​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]US Abdomen Limited[/td][td]
04/05/2019 07:07​
[/td][td]
04/05/2019 10:03​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]US Retroperitoneum Complete[/td][td=bgcolor:#E2EFDA]
04/05/2019 16:43​
[/td][td=bgcolor:#E2EFDA]
04/05/2019 16:51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Brain w/o Contrast[/td][td]
28/05/2018 00:00​
[/td][td]
19/06/2019 14:51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Neck Soft Tissue w/ Contrast[/td][td=bgcolor:#E2EFDA]
02/05/2019 12:47​
[/td][td=bgcolor:#E2EFDA]
19/06/2019 11:28​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Spine Cervical 2-3 Vws Stnd Protocol[/td][td]
15/05/2019 12:00​
[/td][td]
15/05/2019 12:29​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Face w/ Contrast[/td][td=bgcolor:#E2EFDA]
03/05/2019 19:39​
[/td][td=bgcolor:#E2EFDA]
27/05/2019 21:51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PET CT Cardiac Rest/Stress[/td][td]
29/05/2019 13:34​
[/td][td]
12/06/2019 12:36​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PET CT Chest Read[/td][td=bgcolor:#E2EFDA]
29/05/2019 13:34​
[/td][td=bgcolor:#E2EFDA]
29/05/2019 13:34​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Chest w/Contrast (PE Protocol)[/td][td]
29/05/2019 15:50​
[/td][td]
07/06/2019 23:57​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Orbits w/o Cont[/td][td=bgcolor:#E2EFDA]
31/05/2019 12:27​
[/td][td=bgcolor:#E2EFDA]
31/05/2019 12:43​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Barium Swallow (Esophagram)[/td][td]
07/06/2019 14:45​
[/td][td]
07/06/2019 15:12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Face w/o Contrast[/td][td=bgcolor:#E2EFDA]
04/05/2019 05:39​
[/td][td=bgcolor:#E2EFDA]
08/06/2019 11:58​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NC Myocard Perf Rest +Stress SPECT Multi[/td][td]
05/06/2019 15:49​
[/td][td]
12/06/2019 12:26​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]NM Brain / BTO[/td][td=bgcolor:#E2EFDA]
11/06/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
12/06/2019 12:25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Abdomen + Pelvis w/ + w/o IV Contrast[/td][td]
19/05/2019 12:22​
[/td][td]
30/05/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Spine Thoracic w/+ w/o Contrast[/td][td=bgcolor:#E2EFDA]
21/07/2017 00:00​
[/td][td=bgcolor:#E2EFDA]
11/06/2019 10:50​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Spine Lumbar w/+ w/o Contrast[/td][td]
21/07/2017 00:00​
[/td][td]
17/06/2019 14:55​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Head Perfusion[/td][td=bgcolor:#E2EFDA]
02/05/2019 23:50​
[/td][td=bgcolor:#E2EFDA]
18/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Upper Ext w/ Contrast Right[/td][td]
03/05/2019 14:24​
[/td][td]
03/05/2019 14:40​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Spine Lumbar w/o Contrast[/td][td=bgcolor:#E2EFDA]
08/05/2019 13:28​
[/td][td=bgcolor:#E2EFDA]
14/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Head w/ + w/o Contrast[/td][td]
09/05/2019 10:35​
[/td][td]
13/05/2019 14:50​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CTA Abdomen + Pelvis w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
21/05/2019 17:44​
[/td][td=bgcolor:#E2EFDA]
21/05/2019 18:18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Abdominal Outside Image Consult[/td][td]
23/05/2019 15:13​
[/td][td]
23/05/2019 15:38​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRA Head w/o Contrast[/td][td=bgcolor:#E2EFDA]
28/05/2018 00:00​
[/td][td=bgcolor:#E2EFDA]
13/06/2019 17:57​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Femur w/ Contrast Left[/td][td]
29/05/2019 21:00​
[/td][td]
29/05/2019 21:02​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Spine Cervical w/o Contrast[/td][td=bgcolor:#E2EFDA]
10/05/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
14/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NC Chest Read[/td][td]
05/06/2019 15:49​
[/td][td]
05/06/2019 15:56​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Spine Cervical w/o Contrast[/td][td=bgcolor:#E2EFDA]
04/05/2019 05:39​
[/td][td=bgcolor:#E2EFDA]
18/06/2019 10:05​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Orbits w/ + w/o Cont[/td][td]
19/05/2019 00:00​
[/td][td]
16/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Brain Stealth w/o Contrast[/td][td=bgcolor:#E2EFDA]
14/06/2019 16:42​
[/td][td=bgcolor:#E2EFDA]
14/06/2019 17:07​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Knee w/o Contrast Left[/td][td]
17/06/2019 00:00​
[/td][td]
17/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Chest Tube Insert[/td][td=bgcolor:#E2EFDA]
18/06/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
18/06/2019 14:23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Chest w/ + w/o Contrast (Aorta)[/td][td]
19/06/2019 17:25​
[/td][td]
19/06/2019 17:30​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Chest w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
19/06/2019 19:52​
[/td][td=bgcolor:#E2EFDA]
19/06/2019 19:52​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Brain Stealth w + w/o Contrast[/td][td]
02/05/2019 09:56​
[/td][td]
03/05/2019 09:18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Spine Cervical w/+ w/o Contrast[/td][td=bgcolor:#E2EFDA]
21/07/2017 00:00​
[/td][td=bgcolor:#E2EFDA]
01/06/2019 21:05​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Head w/ + w/o Contrast[/td][td]
19/01/2019 00:00​
[/td][td]
14/06/2019 15:56​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Spine Lumbar w/o Contrast[/td][td=bgcolor:#E2EFDA]
13/05/2019 08:55​
[/td][td=bgcolor:#E2EFDA]
13/05/2019 14:01​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Abdomen+Pelvis w/+w/o Cont Abd Intrp[/td][td]
23/05/2019 17:49​
[/td][td]
02/06/2019 15:03​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Abdomen w/ IV Contrast[/td][td=bgcolor:#E2EFDA]
24/05/2019 11:57​
[/td][td=bgcolor:#E2EFDA]
24/05/2019 12:25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Abd Aorta w/ Bil Runoff[/td][td]
24/05/2019 23:56​
[/td][td]
05/06/2019 18:45​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRI Abdomen w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
31/05/2019 08:49​
[/td][td=bgcolor:#E2EFDA]
05/06/2019 16:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Pelvis (GI/GU) w/ + w/o Contrast[/td][td]
05/06/2019 16:00​
[/td][td]
05/06/2019 16:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRA Neck w/o Contrast[/td][td=bgcolor:#E2EFDA]
01/05/2019 19:40​
[/td][td=bgcolor:#E2EFDA]
13/06/2019 17:57​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Neck Soft Tissue w/ + w/o Contrast[/td][td]
06/06/2019 00:00​
[/td][td]
06/06/2019 00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CTA Lower Ext w/ Contrast Left[/td][td=bgcolor:#E2EFDA]
16/06/2019 00:53​
[/td][td=bgcolor:#E2EFDA]
16/06/2019 01:20​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Brain w/+ w/o Contrast + Perfusion[/td][td]
05/04/2019 00:00​
[/td][td]
24/05/2019 14:59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CTA Neck w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
30/05/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
31/05/2019 13:38​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XR Spine Myelogram Cerv +Thor + Lumb[/td][td]
29/03/2019 00:00​
[/td][td]
03/05/2019 15:22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Spine Lumbar w/ Contrast[/td][td=bgcolor:#E2EFDA]
29/03/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
03/05/2019 15:22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CT Spine Thoracic w/ Contrast[/td][td]
29/03/2019 00:00​
[/td][td]
03/05/2019 15:22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]CT Spine Cervical w/ Contrast[/td][td=bgcolor:#E2EFDA]
29/03/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
03/05/2019 15:22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]MRI Neck w/ + w/o Cont[/td][td]
12/05/2019 13:43​
[/td][td]
12/05/2019 14:33​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]MRV Brain w/ + w/o Contrast[/td][td=bgcolor:#E2EFDA]
19/05/2019 00:00​
[/td][td=bgcolor:#E2EFDA]
24/05/2019 10:11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CTA Chest w/ Contrast[/td][td]
04/06/2019 13:15​
[/td][td]
04/06/2019 16:45​
[/td][/tr]
[/table]
 
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,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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