What Is Opposite Of Concatenate? How Do I Unconcatenate Text? -

Sometimes you encounter a situation where a series of text has been grouped into a single cell which can occur because of the way that some computer systems output the information or alternatively, someone has concatenated the information. So how do you reverse this process and split the information up or unconcatenated it?

Splitting up text or unconcatenate it can be done via a number of different methods However, by far the most efficient way to do it is using a function called TEXTSPLIT. Another way which is relatively quick and easy is text to columns, however, as you will see in this article the level of flexibility associated with using that method is significantly lower than TEXTSPLIT. Either follow the directions below or watch the video.

Using TEXTSPLIT To Unconcatenate Text

TEXTSPLIT is generally the most efficient way to split up or unconcatenate text and has several advantages over the older method listed later in this article. It is a new function that has been included in Office 365 for the first time along with several other functions new text functions.

The structure of the TEXTSPLIT function Is as follows;

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

Generally, I only use the first two elements of the function which are suitable in most cases to do what is required. The first element is the text you want to split which is required element to use this function.

The second element, col_delimiter which marks the point the text that marks the point where to spill the text across columns. An example of this is shown in the image below.

It is important to note that even though the function is operating across three cells in the example above you only need to put the formula into the first cell.

However, if you want the text that is split to be placed vertically rather than horizontally the row_delimiter is where you need to place the point at which text to split. The example below shows an example of this use of this part of function.

TEXTSPLIT can also be used to deal with two-dimensional arrays of information which can be created by putting in both a col_delimiter‘ and a row_delimiter simultaneously. An example of where this may come in handy is provided below where TEXTSPLIT has been used to create a two dimensional array.

In addition to these two parameters listed this function also caters for the situation where there are two text separators that appear consecutively in the text you are trying to separate. In this case text split the default of TEXTSPLIT is to create a cell that is empty within the list of separated text. However, it is possible to get text split to ignore the separators when this situation arises.

An example of this is shown below where the text in Cell A1 has been modified to include an additional comma between Microsoft and Samsung. The inclusion of this extra, results in an empty cell appearing in the split text this will occur when either of the following two formulas is put into the cell,

=TEXTSPLIT(A1, “,”) OR =TEXTSPLIT(A1, “,”,, FALSE)

However, if you modify the formula to =TEXTSPLIT(A1, “,”,, TRUE) the blank cell created in D1 will disappear.

The match_mode element of the function allows you to control the whether the delimiter entered into the function is case-sensitive or not. This optional element that you can add to the function with the default being a case sensitive function. To apply this in the function;

0 = A case sensitive match

1 = A case insensitive match

An example of this is shown below Where the delimiter used in the text is z. The formula is used in the example below are as follows;

Case Insensitive =TEXTSPLIT(A2, “z”,,,1)

Case Sensitive =TEXTSPLIT(A5, “z”,,,0)

The final part of theTEXTSPLIT function is the pad_with which is really handy If you receive information that is incomplete in a 2D array. The default value with this element of the function is is #N/A, however this can be replaced with text or numbers as required. An example of this is provided below where there is a 2D array with some information about a student’s score missing from that array.

Effect On Result Returned Without Using The Pad_with Element

Effect On Result Returned Using The Pad_with Element

Text To Columns

The alternative method is to use text to columns, however, this method is no way near as flexible as the TEXTSPLIT function. This is because it can only separate text that is displayed in columns and the other disadvantage is that it will leave the separator in the text cell which is not ideal. To separate text using text to columns;

Step 1: Select the column