I have a web application that I run on a fairly regular basis that allows searching for multiple database records by specifying multiple search items in a single pipe-delimited string. Many times, the values that I want to search for come from a column of data in Excel. So I wrote an Excel macro that would convert data copied from a column into the Windows clipboard into a single pipe-delimited string (and another macro to convert it back).  Since I want the macros to be available for all workbooks/spreadsheets, I created them in my Personal Macro Workbook which is a hidden workbook that gets loaded every time that you run Excel. I am doing this with Excel 2013 but I know it also works for Excel 2010 and should work with future versions as well.

The following is a description of how to implement this into your own copy of Excel.

• Open Excel and go to the View tab
• Click on Unhide in the ribbon bar in order to make your Personal Macro Workbook visible. If the Unhide item is grayed out, you don’t currently have a Personal Macro Workbook and will need to create one.
• If you don’t have a Personal Macro Workbook, click on the Macros drop-down in the ribbon menu and select to record a macro. Select Personal Macro Workbook as the place to store the macro and click on OK. Click on the Macros drop-down in the ribbon menu again and select to stop recording. The Unhide item in the ribbon menu should now be available. Click on it.
• Select PERSONAL.XLSB from the list of workbooks and click OK. The PERSONAL.XLSB workbook will open.
• Go to the View tab and click on the Macros icon in the ribbon bar to view macros.
• If you recorded a macro in order to create the Personal Macro Workbook, select that macro and click the Edit button. Otherwise, in the Macro name: field, enter the macro name of PipeIt and then click on the Create button. This will open the macro editor.
• Select all the text in the editor and delete it.
• Copy and paste the following into the editor (from the Sub PipeIt() line down to and including the End Sub in the PipeToRows macro):

Sub PipeIt()
'
' PipeIt Macro
' Convert row data in clipboard to a pipe-delimited string
'
'
Dim objData As New MSForms.DataObject
Dim strText

bjData.GetFromClipboard
strText = objData.GetText()
strText = Replace(strText, vbCrLf, Chr(124))
If (Right(strText, 1) = "|") Then
 strText = Left(strText, Len(strText) - 1)
End If
objData.SetText strText
objData.PutInClipboard

MsgBox ("Rows in clipboard converted to pipe-delimited string")

End Sub

Sub PipeToRows()
'
' PipeToRows Macro
' Convert a pipe-delimited string in the clipboard to rows of data
'
'
Dim objData As New MSForms.DataObject
Dim strText

objData.GetFromClipboard
strText = objData.GetText()
strText = Replace(strText, Chr(124), vbCrLf) + vbCrLf
objData.Clear
objData.SetText strText
objData.PutInClipboard

MsgBox ("Pipe-delimited string in clipboard converted to rows")

End Sub

• If you get an error because it does not recognize the MSForms.DataObject, you need to add a reference to the Microsoft Forms 2.0 Object Library. To do this, click on Tools->References and check the box for:

Microsoft Forms 2.0 Object Library

If Microsoft Forms 2.0 Object Library is not in the list of available references, then click on the Browse button and go to the C:\Windows\System32 (or SysWow64) directory and select the FM20.DLL file. The library should then be in the list of references that you can then select and add the reference.

• Next, press Ctrl+S to save the macros and then Alt+Q to quit the macro editor.
• Click on the Macros icon in the ribbon bar to open the macro list again.
• Select PipeIt from the list and click the Options… button.
• On your keyboard, hit the key combination that you want for the shortcut key. I use Ctrl+Shift+I because it is not typically assigned to another Excel function.
• Enter a description in the Description box and click OK. The description I used is:

Convert row data in clipboard to a pipe-delimited string

• Select the PipeToRows macro and set the Options for it as well. I use Ctrl+Shift+K and the description I used is:

Convert pipe-delimited string in clipboard to rows of data

• Exit the macro list and then click on Hide in the ribbon menu to hid the PERSONAL.XLSB workbook.

To test it out, create or use an existing spreadsheet with multiple rows of data. Select one of the columns and copy it to the clipboard. Then press your shortcut key combination to run the PipeIt macro (for me it is Ctrl+Shift+I). A message should be displayed informing you that the clipboard data has been converted. Now go to an empty cell on the spreadsheet or to any other application like Notepad and paste from the clipboard. The cell values from the selected Excel column should be in a single string with a pipe character delimiting each value.

Similar macros can be easily created to use comma as the delimiter instead of the pipe character. The comma is ASCII character 44. I made macros, CommaIt and CommaToRows and assigned shortcut keys Ctrl+Shift+Y and Ctrl+Shift+H, respectively. Comma delimiting is useful for SQL queries.