Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Recognize or extract cell info

Topic Labels: Formulas
846 10
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello everyone, first time in this forum but I’ve been using Airtable for a while.

I have an ecommerce and when there is a new order I get this information in my “Purchased items” (cant change this).

count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable

count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure

I would like to extract some info for ecah product, for example the count, productSlug, productName, and put it in other cells.

Is this possible?

Thank you very much!

10 Replies 10

Before I feel comfortable advising you, I’ll need a little bit more information.

How do you get this information - is it presently arriving in a table named “Purchased items” as one big text field?

Hello Bill,

No, this ir arriving (thanks to zapier) in a fild called Order details.
What I want to do is basically separate the different products and knowing how much of each the customer bought.

I also can get this information in this format (simpler I think):

rollin-102-bleu (rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable) x 1 = 1 € ~~ rollin-102-bleu (rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure) x 1 = 1 € ~~ Taxes: 0 € ~~ TOTAL: 2 €

Here there is 1 products

  1. rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable x1
  2. rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure x1

Thank you!

Yep - I understand. You need a string parser that understands the data format being sent into that field. To make an outcome like this -

image

Quantity
MID({Order details}, FIND("count: ", {Order details}) + 7, FIND("productName:", {Order details}) - (FIND("count: ", {Order details}) + 7))

Product Name
MID({Order details}, FIND("productName: ", {Order details}) + 13, FIND("productSlug:", {Order details}) - (FIND("productName: ", {Order details}) + 13))

Product Slug
MID({Order details}, FIND("productSlug: ", {Order details}) + 13, FIND("rowTotal:", {Order details}) - (FIND("productSlug: ", {Order details}) + 13))

OMG this is amazing! Thank you so much!
Is it possible to break in 2/3/4 or whatever this hole thing before applying the formulas you gave me?

I get all this as a whole
"count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable

count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure"

Is is possible to break and put it in a different cell everytime there is a new word “count”?

This is already amazing thank you!

I have no idea what this means. Clarify?

Sure,

each time the word “count” appears, it means that i have a new unique product.

my cell can contain several time this word, if the client buys mire than one unique product.

In this case, they bought 2 unique products one time each:

PRODUCT 1
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable

PRODUCT 2
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure

But if they buy 3 unique products, i can have something like this (always in the same cell):

PRODUCT 1
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable

PRODUCT 2
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure

PRODUCT 3
count: 1
productName: 340
productSlug: rollin-340-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable

For me to use your formulas I have to divide this in 3 different cells, am I right?

Yes, either cells or separate records.

Ideally, each product purchased should be a separate transaction record with an identifier (such as invoice number) to allow you to group-by for a given order.

In any case, the data flowing in from your transaction system is doing a poor job of arranging the data for consistent parsing and management.

Can I separate this with a formula?

I know…

Possibly, but this would be very difficult and complex if at all possible.

If it were me faced with this little nightmare, I would not use formulas - I would create an integration layer that handles all parsing to create clean data in Airtable that also conforms to your data management requirements.

Thank ok very much for all your answers @Bill.French!