Google Sheets: Pasting Delimited Data

The National Emergency Medicine Services Information System (NEMSIS) is a data standard for patient care information corresponding to a 911 call or other emergency care situations(such as being treated for a medical condition at a NASCAR event). The schemas associated with NEMSIS are delineated as federal (national) and state. The elements of this schema are defined at Required National and Recommended State Elements (V3.5.0) where the definitions are a value separated list where the separator is the vertical bar (|) character.

An example of this data is as follows:

Rather than save the data to a file and then import, the data can simply be cut and pasted into a clean Google Sheet:

Notice the past icon (the clipboard) with a down triangle indicating a menu. Clicking on the down triangle displays a menu from which Split text to columns should be selected:

Clicking on Split text to columns displays the following:

Clicking on Detect automatically displays the following:

In the Custom separator textbox enter a vertical bar:

Click on enter and the delimited data will populate the columns using the vertical bar character as a separator:

As an alternative, the Data menu contains Split text into columns which could have been used to separate the data using the vertical bar.

The delimited data values each contain a leading and trailing single quote. To remove these click the top left of the sheet (to the left of A and above 1) to select all data elements:

From the Edit menu select Find and replace:

Using Find and replace the single quotes can be removed: