VBA routines for splitting and joining text

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.
This entry was posted in Excel, UDFs, VBA and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.