Recognize or extract cell info

[Sebastian_Orell]

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!

[Bill_French]

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

Sebastian_Orellano1:

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

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

[Sebastian_Orell]

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!

[Bill_French]

Sebastian_Orellano1:

separate the different products and knowing how much of each the customer bought.

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

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))

[Sebastian_Orell]

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!

[Bill_French]

Sebastian_Orellano1:

Is it possible to break in 2/3/4

I have no idea what this means. Clarify?

[Sebastian_Orell]

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?

[Bill_French]

Sebastian_Orellano1:

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.

[Sebastian_Orell]

Bill.French:

Yes, either cells or separate records.

Can I separate this with a formula?

Bill.French:

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.

I know…

[Bill_French]

Sebastian_Orellano1:

Can I separate this with a formula?

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.