I have a column that contains a bit of data separated by commas. For example:
ROW 1: Teeth Cleaning, $150, John Smith
ROW 2: X-Ray, $400, Jane Doe
All this data is in one column because that is the way it is imported using zapier.
Now that the data is imported, I’d like to break the data up by the comma bracket. I’d figure this would involve creating 3 new columns that are formula generated:
Does anyone know of a formula that will pull data before the first comma for the service name and then for the second column have a formula that pulls data after the first comma and before the second comma?
You can definitely use formulas for this, but if you don’t mind using an extra step in Zapier, you can also do this before you import the data, which I would recommend as it keeps things on the Airtable side more simple.
In Zapier, add a step and choose “Formatter” as the option, then choose “Text”. From the “Edit Template” screen, choose “Split Text”, and use “,” as your delimiter. There is an option to export all of the data as three separate fields. You can then use those to import into Airtable.
I am trying to get this but only some of my strings have 3 items. Some have 2, some have one. Using the current formulas, the middle and third items are not pulling right. Any ideas on how to split 1 field with either 1, 2, or 3 items in a string into three independent fields?