ICU Medication Example

Excel Data Mining Using Text to Columns and PivotTables

I've been working with a hospital ICU unit that wants to explore the relationship between the use of various sedatives and delirium. Delirium occurs in 45-87 percent of patients; that's 4-8 out of every 10. While sedation can keep a waking patient from ripping out a ventilator tube, it can also cause psychological trauma.

Data Example

Every time a patient gets a dose of medication, it goes into their medical record. How would we figure out how much they received using data that looks like this:

icu medication sample data Excel

The data set shows:

  • the type of sedation
  • dose
  • volume
  • delivery mechanism

However, everything is in one cell. I have found that most of this kind of data requires some preprocessing to get it into a form that can be summarized.

Text to Columns

To get to the data we will need to separate the various parts into a table that we can summarize using Excel's PivotTable. We can use Excel's "Text to Column" data tool to start the process:

Excel will prompt for a delimiter because this field isn't fixed width; it's free- form:

If we choose a blank space, Excel will break each line up into individual words or phrases:

Data Alignment Using Cut and Paste

Some of the cells may need to be realigned to get dose, volume and method in the correct column. Dexmedetomidine has the words "in 0.90% NACL" in-between the medication and dose. So just cut and paste the data into the correct columns:

The transformed data now has everything in almost the same column. We will need to do the same thing for LORazepam "UD":

Now we have the dosage (e.g., 4MCG) but we still can't summarize the amounts. We need to split the dose into a number and size.

To do this we need to insert a column after dose and use Text-to-Columns to split the dose on the letter "M":

text to columns wizard

Paste Special Divide

Then, we might have to adjust the numbers a little bit because 1 MCG (micro gram) is 1 milligram/1000. To do this, we can use Excel's Paste Special function to divide the dose (B2:B3) by 1000 (G2):

Now we have the correct dosages in milligrams (MG)


Using QI Macros PivotTable Wizard we can select cells A1:B1 and get a sum of all medications.

I've removed the patient's identification, but we could summarize the medications by patient as well.

Or if we had the dates, we could get a control chart of medication usage by day or week:

pivot table example


It's pretty clear which medication is used in higher doses...Propofol.

Changing from Sum to Count

If we go back to the PivotTable and double click on cell A1, Excel's PivotTable will ask how we want to summarize the data. If we change to a "Count" instead of a sum:

We get a different picture of medication usage:

Lorazepam is the most frequent choice for sedation. If we wanted to know more about the dose and method of delivery, we could double click on the PivotTable cell containing the Lorazepam count (B3) to get all of the underlying data:


We could then use PivotTables to summarize the data by dose and method:

Note: I used Dose as both a Row Label and data value. Most people are unaware that PivotTable fields can be used multiple times. We could then use QI Macros Pareto Chart to chart dosages:

Vials (2MG) are the most common way Lorazepam is delivered.

Which one causes more delirium? Is it the type of sedation or the dosage? Is it the amount of time a patient spends on sedation? Teams are working on the answer as we speak using the ABCDE process. See for more information.

Here's My Point

Sometimes the data you need for analysis can be tightly packed into a single cell. Using Excel's Text-to-Column and PivotTable functions you can data mine the answers you need. Don't let the data intimidate you. There's always a way to get at it.

Free Lean Six Sigma Yellow Belt TrainingTake our FREE Lean Six Sigma Yellow Belt training online.