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 -

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!