Sajid Ahmed
The Split Text to Columns feature in Google Sheets is a dynamic tool. This tool or the SPLIT function allows us to divide any values into separate columns. However, occasionally you could discover that the process isn’t working as planned. In this article, I’ll show you 3 useful fixes when the Split Text to Columns operation is not working in Google Sheets with clear steps and images. At last, you’ll get output like the below picture.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
3 Useful Solutions When Split Text to Columns Operation Is Not Working in Google Sheets
Let’s get introduced to our dataset first. Here we have some full names in Column B of our dataset. We want to split these names into first names and last names into Column D and Column E respectively. We have applied the Split Text to Columns operation in Column D but as you can see in the picture the operation isn’t working. So, we’ll see 3 useful fixes when the Split Text to Columns operation is not working in Google Sheets by using this dataset.
Solution 1: Inserting Proper Delimiter
We have to insert the proper delimiter in order to get output from the Split Text to Columns operation in Google Sheets. The operation can be done in 2 separate ways. We can use the Split Text to Columns tool or the SPLIT function. In either case, we have to use space, comma, semicolon, period, or any custom delimiter properly to get the output. Below we’ll see the ways one by one.
1.1 For Split Text to Columns Tool
When we use the Split Text to Columns tool we may find that it isn’t working because of not inserting a proper delimiter as you can see in the picture. We don’t put any delimiter between texts in Column B of our dataset. So, the Split Text to Columns tool can’t separate the names. So, we have to insert space, comma, semicolon, period, or any custom delimiter to separate the names in Columns D and E.
Steps:
- Firstly, put space as a delimiter between texts in all the values of Column B manually.
- Secondly, select all the cells from Cell B5 to B10 and press Ctrl+C from the keyboard to copy them.
- Then, activate Cell D5 and press Ctrl+V from your keyboard to paste the values as it is in Column D.
- Next, select all the cells from Cell D5 to D10.
- After that, go to Data > Split Text to Columns.
- Thereafter, you’ll get a menu named Separator.
- Under this Separator menu, there are 6 options from which you have to give your choice.
- Consequently, choose Space from the option because we have used space as a delimiter.
- Moreover, you can use a comma, semicolon, period, or custom sign as your delimiter.
- Finally, you’ll see that the Split Text to Columns operation is working nicely. The names are separated and placed in Columns D and E as first name and last name respectively.
Read More: How to Split Cell by Comma in Google Sheets (2 Easy Methods)
1.2 For SPLIT Function
We can also use the SPLIT function to split the text into columns in Google Sheets. But as in our case, we have inserted the following formula and it isn’t working-
=SPLIT(B5,,)The formula isn’t working because we have to put a delimiter into our formula. We can use space, comma, semicolon, period, or any custom delimiter in the SPLIT function to get the output. Let’s see the steps below.
Steps:
- At first, manually insert spaces between all of the values in Column B as delimiters.
- Then, write the following formula in Cell D5–
=SPLIT(B5," ")- Next, hit Enter to get the output as split text.
- After that, apply the Fill Handle tool to use the formula in the rest of the cells of Column D.
- In the end, you’ll notice that the procedure to separate the text into columns is running smoothly. The first name and last name of each person are separated and placed in Columns D and E, respectively.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
Similar Readings
- How to Split String into Array in Google Sheets (3 Easy Methods)
- Split Address in Google Sheets (3 Easy Methods)
- How to Split a Cell in Google Sheets (9 Quick Methods)
Solution 2: Using Custom Delimiter
Now another problem arises when we choose space as our delimiter as you see in the below image. The names are separated into 3 columns, Columns D, E, and F serially. The Slash Sign (/) resides in Column E. It happened because there were spaces on both sides of the Slash Sign (/). But we only want to separate the first and last names. In this case, we have to use the Custom Delimiter option to get only the first name and last name in Columns D and E.
Steps:
- First of all, select Custom under the Separator menu.
- Then, insert space/space as the custom delimiter into the box.
- You have to put spaces before and after the Slash Sign (/) otherwise, the delimiter won’t work.
- In the end, you’ll see that each person’s first and last name is displayed separately in Columns D and E.
Read More: How to Split Text to Columns Using Formula in Google Sheets
Solution 3: Remove Any Filled Cell Within the Range
Apart from the previous method, we have some values in Cell E5 as the following image.
So when we use the SPLIT function it is giving a #REF! error saying that the result isn’t expanded because there is a value in Cell E5. It means when we have any value in our output range then the SPLIT function won’t work. We have to remove the value from our range to get results.
Steps:
- In the first place, remove the value from Cell E5 manually by pressing the Delete key from your keyboard.
- Then, type the following formula in Cell D5–
=SPLIT(B5,"/")- Next, press Enter to get the separated names in Columns D and E.
- Consequently, use the Fill Handle tool for the remaining cells in Column D to utilize the formula there.
- Ultimately, you’ll notice that Columns D and E display each person’s first and last names separately.
Read More: How to Split Cells to Get Last Value in Google Sheets (4 Methods)
Conclusion
That’s all for now. Thank you for reading this article. In this article, I have discussed 3 useful fixes when the Split Text to Columns operation is not working in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.
