How to Get Opposite of Concatenate in Google Sheets (2 Ways)

Suppose you are working on a dataset where you get combined values. But while executing your work you need separated values in the dataset. So, if you try to separate all the data manually then this process will be very time-consuming or if the dataset is large then completing this process will be nearly impossible. In this case, you need to split the values to get the desired value. Here, we will learn how to get opposite of concatenate in google sheets.

Here is the overview of this article. You will learn more about this topic if you go through the whole article. So, let’s start.

Table of Contents

A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.

Opposite of Concatenate

What Is Concatenated Value in Google Sheets?

While working on a dataset sometimes we need to combine some values together. When you combine more than one value in a cell or dataset, you can call the values as concatenated value. For example, if a dataset contains (Name, Roll Number) in the same cell then we call it a combined value. But if we can write these two values in different cells then these values are the opposite of concatenated values.

2 Simple Ways to Get Opposite of Concatenate in Google Sheets

The Dataset below contains Company Name, Current Month, and Sales. Here, the dataset represents the sale of the current month in different companies. But the data is combined and challenging to understand. Here we will split the concatenated values and get the opposite of concatenated values in google sheets.

1. Using SPLIT Function

If you want to get the opposite of concatenate then you must split the value. Here we will use the SPLIT function in google sheets to get desired values.

📌 Steps:

First, select cell C5 to enter the formula.

Then enter the SPLIT function to split the combined values from the dataset.

After that, select cell B5 as the lookup value.

Now, write down the separator, here (,) and enclose it with double quotes.

=SPLIT(B5,",")

Here, you get the opposite of the concatenate values from the dataset.
Finally, drag down the fill handle and complete the process.

Similar Readings

2. Applying Split Text to Columns Option

Therefore, we will apply Split text to columns option here to get the desired value in google sheets using the same dataset shown before.

📌 Steps:

Initially copy the dataset in range C5:C11 to conduct this process.
Here, the dataset is in range B5:B11 and in range C5:C11 as below.

Now select range C4:C11 to execute the process. Here, cell C4 is the header row and the header row will also split from the concatenated value.

Consequently, select Data >> Split text to columns from the menu bar to split the combined data. After that, the data will be very easy to understand.

Lastly, after clicking on Split text to columns option, we will be able to split the values by a comma (,) as below.

There is the final output of this process.

Things to Remember

You can use the SPLIT Function the value is connected with wildcards.
You can use both function and google sheets methods to execute this formula.

Conclusion

In this article, we explained how to get opposite of concatenate in Google Sheets with practical examples. Hopefully, the methods will help you apply these formulas to your dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.