{"id":50,"date":"2018-04-04T20:20:42","date_gmt":"2018-04-04T20:20:42","guid":{"rendered":"http:\/\/pfool.com\/blog\/?p=50"},"modified":"2018-04-04T20:20:42","modified_gmt":"2018-04-04T20:20:42","slug":"excel-macro-to-convert-column-data-to-pipe-delimited-string","status":"publish","type":"post","link":"https:\/\/pfool.com\/blog\/2018\/04\/04\/excel-macro-to-convert-column-data-to-pipe-delimited-string\/","title":{"rendered":"Excel macro to convert column data to pipe-delimited string"},"content":{"rendered":"<p>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).\u00a0 Since I want the macros to be available for all workbooks\/spreadsheets, I created them in my <strong>Personal Macro Workbook<\/strong> 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.<\/p>\n<p>The following is a description of how to implement this into your own copy of Excel.<\/p>\n<ul>\n<li>Open Excel and go to the <b>View<\/b> tab<\/li>\n<li>Click on <b>Unhide<\/b> in the ribbon bar in order to make your <strong>Personal Macro Workbook<\/strong> visible. If the <strong>Unhide<\/strong> item is grayed out, you don&#8217;t currently have a <strong>Personal Macro Workbook<\/strong> and will need to create one.<\/li>\n<li>If you don&#8217;t have a <strong>Personal Macro Workbook<\/strong>, click on the <strong>Macros<\/strong> drop-down in the ribbon menu and select to record a macro. Select <strong>Personal Macro Workbook<\/strong> as the place to store the macro and click on <strong>OK. C<\/strong>lick on the <strong>Macros<\/strong> drop-down in the ribbon menu again and select to stop recording. The <strong>Unhide<\/strong> item in the ribbon menu should now be available. Click on it.<\/li>\n<li>Select <b>PERSONAL.XLSB<\/b> from the list of workbooks and click <b>OK<\/b>. The PERSONAL.XLSB workbook will open.<\/li>\n<li>Go to the <b>View<\/b> tab and click on the Macros icon in the ribbon bar to view macros.<\/li>\n<li>If you recorded a macro in order to create the <strong>Personal Macro Workbook<\/strong>, select that macro and click the <strong>Edit<\/strong> button. Otherwise, in the <b>Macro name:<\/b> field, enter the macro name of <b>PipeIt<\/b> and then click on the <b>Create<\/b> button. This will open the macro editor.<\/li>\n<li>Select all the text in the editor and delete it.<\/li>\n<li>Copy and paste the following into the editor (from the Sub PipeIt() line down to and including the End Sub in the PipeToRows macro):<\/li>\n<\/ul>\n<pre>Sub PipeIt()\r\n'\r\n' PipeIt Macro\r\n' Convert row data in clipboard to a pipe-delimited string\r\n'\r\n'\r\nDim objData As New MSForms.DataObject\r\nDim strText\r\n\r\nbjData.GetFromClipboard\r\nstrText = objData.GetText()\r\nstrText = Replace(strText, vbCrLf, Chr(124))\r\nIf (Right(strText, 1) = \"|\") Then\r\n strText = Left(strText, Len(strText) - 1)\r\nEnd If\r\nobjData.SetText strText\r\nobjData.PutInClipboard\r\n\r\nMsgBox (\"Rows in clipboard converted to pipe-delimited string\")\r\n\r\nEnd Sub\r\n\r\nSub PipeToRows()\r\n'\r\n' PipeToRows Macro\r\n' Convert a pipe-delimited string in the clipboard to rows of data\r\n'\r\n'\r\nDim objData As New MSForms.DataObject\r\nDim strText\r\n\r\nobjData.GetFromClipboard\r\nstrText = objData.GetText()\r\nstrText = Replace(strText, Chr(124), vbCrLf) + vbCrLf\r\nobjData.Clear\r\nobjData.SetText strText\r\nobjData.PutInClipboard\r\n\r\nMsgBox (\"Pipe-delimited string in clipboard converted to rows\")\r\n\r\nEnd Sub<\/pre>\n<ul>\n<li>If you get an error because it does not recognize the MSForms.DataObject, you need to add a reference to the <b>Microsoft Forms 2.0 Object Library<\/b>. To do this, click on Tools-&gt;References and check the box for:<\/li>\n<\/ul>\n<p>Microsoft Forms 2.0 Object Library<\/p>\n<p>If <b>Microsoft Forms 2.0 Object <\/b>Library is not in the list of available references, then click on the <b>Browse<\/b> 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.<\/p>\n<ul>\n<li>Next, press Ctrl+S to save the macros and then Alt+Q to quit the macro editor.<\/li>\n<li>Click on the Macros icon in the ribbon bar to open the macro list again.<\/li>\n<li>Select <b>PipeIt<\/b> from the list and click the <b>Options\u2026<\/b> button.<\/li>\n<li>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.<\/li>\n<li>Enter a description in the <b>Description<\/b> box and click <b>OK<\/b>. The description I used is:<\/li>\n<\/ul>\n<p>Convert row data in clipboard to a pipe-delimited string<\/p>\n<ul>\n<li>Select the <b>PipeToRows<\/b> macro and set the Options for it as well. I use Ctrl+Shift+K and the description I used is:<\/li>\n<\/ul>\n<p>Convert pipe-delimited string in clipboard to rows of data<\/p>\n<ul>\n<li>Exit the macro list and then click on <b>Hide<\/b> in the ribbon menu to hid the PERSONAL.XLSB workbook.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-50","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/posts\/50","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/comments?post=50"}],"version-history":[{"count":1,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/posts\/50\/revisions"}],"predecessor-version":[{"id":51,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/posts\/50\/revisions\/51"}],"wp:attachment":[{"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/media?parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/categories?post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pfool.com\/blog\/wp-json\/wp\/v2\/tags?post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}