OpenClinica User Manual/TextManipulation

Manipulating text for use in a CRF

Text is frequently copied into the Excel CRF from other programs (such as an electronic copy of the CRF or formulas from other spreadsheets). This can bring formatting issues along with the text. The text can be 'pasted as values' to remove the formatting.

Altering the options in Excel/Word

'Pasting as Values' is a common action with OpenClinica CRFs. In Excel 2010, the keyboard shortcut is: Alt+h, v, v (Home Ribbon, Paste, Paste as Values).

You can add specific buttons to the Ribbon, in 2010, you select: File, Options, Customise Ribbon. You can then add a new custom group (e.g. 'Custom Clipboard'), and add a paste button to it, such as 'Values'.

Using notepad

Text can be pasted into notepad (removing formatting), then any unwanted text (strange characters, tabs etc.) can be removed. Then select all the text in notepad and copy it to the CRF.

The 'Text Calculator'

The Text Calculator spreadsheet quickly reformats paragraphs and tables of text from Word, PDFs or emails. It takes the text list and reformats it in a number of different ways:

1. HTML paragraphs
2. HTML paragraphs (with grey text)
3. A single string
4. A response option text and response values list for radio buttons or dropdown (commas in the text are escaped automatically)
5. A response option text and response values list for radio buttons or dropdown, with an additional default value of (select)

Generating a response option text list

For example, by pasting a list like this:

• Very good
• Good
• Moderate
• Poor
• Very poor

It generates OpenClinica response lists for radio buttons (escaping any commas):

• Very good,Good,Moderate,Poor,Very poor
• 1,2,3,4,5

And response lists for dropdown lists with a default value:

• (select),Very good,Good,Moderate,Poor,Very poor
• ,1,2,3,4,5

Commas in the text are escaped automatically for up to 20 values, and for the dropdown list cells, an additional (select) option is added as the first option in the list.

Generating paragraphs

It also takes text like this:

• Paragraph 1
• Paragraph 2
• Paragraph 3

And adds <p> (paragraph) tags to create an HTML version that can be used in OpenClinica, for example in a header.

How to use it

Use Excel's 'paste as text' option to paste into the spreadsheet under where it says 'Text'. The results appear under the labels 'HTML grey paragraph', 'HTML black paragraph', 'Single string', 'Response options text (no default)', 'Response values (no default)', 'Response options text (with default)' and 'Response values (with default)'. Copy and paste the contents of these cells into an OpenClinica CRF spreadsheet as needed. The spreadsheet can be extended and altered as desired.

As the ordering of pasted text from a heavily formatted PDF can be unpredictable, it can be useful to copy the text to Notepad or a Word document (which also has the advantage of a spellchecker!) before copying to the spreadsheet.