Excel String Tricks: Unravel the Opposite of Concatenate Function in Excel

How to Reverse Concatenation

Text to Column

Text to Columns is a feature in Excel that can be used to split data into separate columns. Follow the steps below to know how to split text –

STEP 1: Highlight the cells containing the combined data that you want to split.

STEP 2: Go to the Data tab and select the Text to Columns button.

STEP 3: In the dialog box, select Delimited.

STEP 4: Check the box of the delimiters that are separating the data.

STEP 5: Input the destination cell and press Finish.

Excel will split the single column into 2 different columns.

Flash Fill

Flash fill is an important feature that is used to split data. You can type the first few rows manually, and Excel will then detect the pattern and complete it for the other rows. It removes the need for creating any complex formula to split data. It can adjust to any pattern, be it names, dates, or any other alphanumeric combination.

Follow the steps below to learn how to use flash fill –

STEP 1: Begin to split the data manually. For example, if you want to split full names in 2 columns, you should type the first name in column B.

STEP 2: Do the same for the second row, and you will see that Flash Fill will complete the rest for you.

STEP 3: If the suggestions are correct, you can press Enter.

Flash Fill is not dynamic, i.e., the result will not change if the source data changes.

TEXTSPLIT function

The TEXTSPLIT function is used to split text into multiple columns. This split is based on a specified delimiter, like a comma, a space, dashes, etc. Let us see how –

STEP 1: Click on the cell where you’d like your split text to appear.

STEP 2: Use the function =TEXTSPLIT(source_cell, “delimiter”). Here, the source data will be the period date and delimiter will be ‘-‘.

STEP 3: Hit Enter. The Date will spill into the month and year.

FAQs

What is Text to Column?

Excel can automatically split data based on specific delimiters using the Text-to-Columns feature. You can choose the delimiter that is present in your data, like space, comma, colon,m tab, etc.

How to reverse concatenate without formulas?

Flash Fill is a feature in Excel that is used to split data. You need to split the data manually for a few rows. It will detect the pattern and complete it for the rest of the rows.

What is the opposite of concatenate?

The opposite of the CONCAT() function in Excel is typically a process that splits combined data back into separate components. This can be achieved using the Text-to-Columns feature, the ‘Flash Fill’ tool, or the TEXTSPLIT() function.

How to separate first and last name in Excel?

You can use the Text to Column feature in Excel to separate first and last names. The delimiter that has to be used is a space that is in between the first and last names.

How to split data in a cell in Excel?

You can split data in Excel by using the Text to Column feature. It is used to split text into multiple columns based on a delimiter like space, comma, tab, etc. You can also use Flash Fill to separate text into different columns.