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.

Overview of Getting 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.

A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.

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.

Dataset to get opposite of concatenate 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.

selecting cell C5

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

inter the function

  • After that, select cell B5 as the lookup value.

enter the lookup value

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

completing formula to get opposite of concatenate in google sheets

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

Drag down the fill handle

Read More: How to Use CONCATENATE Function in Google Sheets

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.

Preparation for applying another method

  • 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.

selecting range

  • 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.

select split text to columns from menu bar

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

separating comma

  • There is the final output of this process.

Final output to get opposite of concatenate in google sheets

Read More: How to Concatenate Two Columns in Google Sheets

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.


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.

Related Articles



This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply