Skip to main content

A formula to pull data from certain comma section

  • July 10, 2019
  • 2 replies
  • 115 views
A
  • Anonymous

Hello,

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:

Service Name
Price
Patient Name

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?

Thanks for your help!

Nathalie_Collin

2 replies

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • July 10, 2019

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.

If you’d rather go the formula route…

First part:

LEFT({Field Name}, FIND(",", {Field Name}) - 1)

Middle:

MID({Field Name}, FIND(",", {Field Name}) + 2, (FIND(",", {Field Name}, FIND(",", {Field Name}) + 2) - (FIND(",", {Field Name}) + 2)))

End:

RIGHT({Field Name}, LEN({Field Name}) - FIND(",", {Field Name}, (FIND(",", {Field Name}) + 2)) - 1)

Hope that helps!

Justin_Barrett
R
N
  • Nolan_Hausler
  • New Participant
  • February 6, 2021

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.

If you’d rather go the formula route…

First part:

LEFT({Field Name}, FIND(",", {Field Name}) - 1)

Middle:

MID({Field Name}, FIND(",", {Field Name}) + 2, (FIND(",", {Field Name}, FIND(",", {Field Name}) + 2) - (FIND(",", {Field Name}) + 2)))

End:

RIGHT({Field Name}, LEN({Field Name}) - FIND(",", {Field Name}, (FIND(",", {Field Name}) + 2)) - 1)

Hope that helps!


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?

JM1234
A
W
Terms & ConditionsAccessibility statement