Best answer

How to split text into columns (correctly)

  • 26 March 2021
  • 2 replies
  • 1655 views

Userlevel 1

I am trying to use Formatter>Text>Split Text to split a single field into multiple columns. This works currently with a comma set as the delimiter but later when I tested the parsing with a field with a comma in it, it split that one field into two separate columns in error. 

Here’s what the originating data looks like when I click to select it in Zapier:


And here’s what the output looks using only a comma as the delimiter, once it has been sent to a Google sheet:
 


You can see that the first row of data came over fine but on the second one I filled out the form with a comma in the first two fields (in the source program) and when Zapier captured it then processed in “Split Text”, it split the fields at that comma, resulting in 4 columns where there should only be 2, pushing the rest out to the right. 

Is there any way to split this out more accurately with a better delimiter or a different function besides Split Text? I tried splitting by “],[“ and also “,” and ‘”,”’ but all of these are not resulting in any split columns. 

If I specify no delimiter, the data gets sent to the google sheet as a single string WITH commas but WITHOUT the brackets and quotes. 

Seems like with it showing those fields like it is in “Input” I should be able to split it better but I’m not sure how to do this? If I could, I’d just change the delimiter on the source application but I don’t have that flexibility. Any help would be appreciated!! I feel like I’m starting to understand the power of Zapier but I need someone to teach me the ways of the force. Thanks! - Joshua 

icon

Best answer by Troy Tessalone 26 March 2021, 15:45

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

2 replies

Userlevel 7
Badge +14

Hi @BROMETHIOUS 

Check out this related Topic:

You may have to use some Formatter > Utilities > functions to handle the data if there is a comma present: https://zapier.com/help/doc/how-use-formatter-functions

Userlevel 1

Wow, thanks @Troy Tessalone that did it. I used Formatter>Utilities>Line-item to Text. Added a hyphen after the input to keep null values and used a custom delimiter. The field with a comma is still there. Now just have to split it out by better delimiter. Thanks! Joshua

Here’s a screenshot of the action: