As mentioned in the previous post, I have written two short VBA routines to aid the process of splitting a column of text strings into separate columns, using either a space or any other chosen character as the delimiter. These routines have been added to the Text-in2 spreadsheet, along with a new JoinText function to reverse the process. The new file can be downloaded (including full open source code) from:
For an example of the use of the new routines see the Txt2Col sheet:
Text (including text from pdf files) can be copied and pasted anywhere. Select all the rows and as many columns as you want to split, then press Alt-F8, select Text2TextCols, and click Run:
The text in the first column is split into the selected columns in text format, so that the original number formats are retained:
The ResetTxt2Cols macro is for use when the Excel Text to Columns wizard has been used, and you want to paste text copied from external files into a single column. To run press Alt-F8, select ResetTxt2Cols, and click run.
Split text (or any other text in a continuous column or row) can be combined with the JoinText user defined function (UDF) as shown below:
JoinText has two optional arguments:
- Separate defines the separator to add between cell contents (default a single space).
- IgnoreBlank ignores blank cells if set to true.
Excel 2016 now has two new built in functions providing similar functionality, Concat() and TextJoin(). The JoinText UDF still has a couple of advantages however:
- It will work in any version of Excel that supports VBA.
- The Separate and IgnoreBlank arguments are optional, simplifying use when the default values are to be used.