How to split cells in Google Sheets?
In Google Sheets, you may need to split the text for analysis. In Google Sheets, you may need to split the text for analysis. Let's explain this with an example. Let's say we have a table with a phone list. We want to know which subscription is being used. For this, let's take the first three digits of the phone number (varies by country)
If our table has a dynamic structure, that is, if we make continuous additions; the method used to split the text will change. If we want to create a dynamic table, we need to divide using function. Let's get into it.
How to split text using 'split the text into columns ' in the menu?
When working with Google Sheets, we may need to split text for analysis. For instance, let's say we have a table with a phone list.
'Split text to columns'-> If we use this method, the splitting will not occur when a new row is inserted.
- Select the column we want to split
- Menu->Data-> Split text to columns
When dividing, we can specify from here how it should be divided. Here, I will use "-". I choose Custom and enter "-"
If we enter new data and try to continue the division, the division will not occur.
How to split text using the SPLIT function?
Every time new data is added to our table, we may want the division applied to be valid. In this case, we need to use the '=SPLIT ()' function. When we pull down the previous row every time a row is added, it will apply the formula to the newly added row.
Let's analyze it with an example.
We may want to split and analyze the Product IDs. When divided, they will be split into a separate column. That's why we go to the column we want to be written.
This is the syntax for the =SPLIT() function.
Text->The cell you want to split. In our example, I'm going to split column A, so I choose A2.
Delimiter-> The token you will use to separate. Data can be comma(,), period(.), space( ), etc. they may be separated. In my example, it is separated by (-).
Split by each-> Determines whether the delimiter will split around each character. Does not default to true
Remove empty text-> Specifies whether or not spaces are removed from split text --> optional
When we complete our function, the new version of our table will be as follows.
=SPLIT(A2,"-")
Now that our first row is split, you can drag down the desired column and apply the split to the other rows, or you can do it for the whole row like me.
We come to the last line and add new data. If you want it to be split in the newly added line, just pull the top row down.
Google Spreadsheet will do the splitting for the last inserted row.
Separation is complete. Now I can find out the number of my products in the furniture category. For this, we need to use the =COUNTIF() function. You can find a detailed article on this subject here ->"How to use COUNTIF function in Google Sheets?"
In order to search under more than one condition, I need to use COUNIFS. For example, I might wonder about both furniture and chairs. You can find a detailed article about it here. ->” How to use COUNTIFS function in Google Sheets?”
