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:

Text-in2.ZIP

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.