iSearch tip: Converting iSearch PMID / GRANT field ‘;’ separated to list

Some data fields downloaded from iSearch contain multiple values in a single cell, separated by a ‘;’. This format can be difficult to manipulate in Excel.

This tip sheet provides a way to convert these single cells into multiple values in a column with each value given the same label as the original row e.g. one row for each publication ID (PMID) for a Core Project Number or ApplID:

Input:

R01DA040411 / 27797688;27238067;26864618
R01MH103770 / 27121977

Output:

R01DA040411 / 27797688
R01DA040411 / 27238067
R01DA040411 / 26864618
R01MH103770 / 27121977

There is also an extension example for unwinding a spreadsheet with multiple fields that you wish to have on the unwound spreadsheet.

INSTRUCTIONS

  1. Download data from iSearch.
  2. If you have downloaded more than just the two columns of interest, copy the two, e.g.Grantnumber and PMID (or the label field and ‘;’ separated field) into a new sheet with headers.
  3. Next run theVisual Basic Macro below. Hold down theAlt+F11keys in Excel which will open the Microsoft Visual Basic for Applications window.
  4. ClickInsertModule, and paste the following macro in the Module Window.

‘Convert text to columns for delimited cell

Sub ConvertTableToList()

Columns("B:B").Select

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True

‘ Convert rows to columns by Column a label

Const TEST_COLUMN As String = "A"

Dim iAs Long, j As Long

Dim iLastRow As Long

Dim iLastCol As Long

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For i = iLastRow To 2 Step -1

iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column

For j = iLastCol To 3 Step -1

.Rows(i + 1).Insert

.Cells(i + 1, 2).Value = .Cells(i, j).Value

.Cells(i, j).Value = ""

Next j

Next i

.Rows(1).Delete

End With

Application.ScreenUpdating = True

End Sub

  1. Press theF5key to run this macro.

This will convert the cells to a list which can then be tidied up by filling in the blanks. To fill in the blanks, carry out the following steps:

  1. Select column A on the worksheet (stopping at the last value in column B)
  2. Press Ctrl+G > Special > Blanks > OK. This process will select all blank cells in range
  3. With the selection in place, press the = key
  4. Press the up arrow key
  5. Press Ctrl+Enter. You will now see all blanks cells filled up the values just above.
  6. Select column A, copy the range and paste them as values (Alt+E+S+V)

EXTENSION

A more complicated example would be something like this:

author1 / author2 / weight / pmids
Souza, Richard Brian|9176018 / Lane, Nancy E|1864757 / 2 / 25139720;25716211

Which you want to convert to this:

author1 / author2 / weight / pmids
Souza, Richard Brian|9176018 / Lane, Nancy E|1864757 / 2 / 25139720
Souza, Richard Brian|9176018 / Lane, Nancy E|1864757 / 2 / 25716211

In this case there is no unique ID (such as ApplID, or Grant Number and you need to copy multiple fields (author1, author2 and weight) onto the unwound data (pmids).

1.Create a unique ID for each row. It doesn't matter what, so you could just use 1,2,3,4.... etc.

2.Create a spreadsheet of unique ID and pmids

3.Carry out the unwind instructions on this worksheet starting from step 3 above, filling the blank rows in unique ID as per instructions from step 6.

4.Once you have an unwound sheet of unique ID and pmids, you can then copy across author1, author2 and weight using vlookups from your original spreadsheet. See Excel tip 1 ( for step by step instructions.

If you are having problems, contact OPA training:

OPA_T#973_July-07-2017