Text to columns using column names already in the serial text list

[Mike_Sobol]

Hi folks,

I’ve searched for an answer to this and have yet to find it. Will consider hiring someone to handle this use case, too.

My e-commerce platform allows me to create a number of custom fields that are essential for me to use in several ways, but it provides no way to edit them in bulk. In fact, it will only export those fields in a single cell like this:

AWG=10;Color=Black;“Number of Conductors=4”;“Conductor type=Stranded”;CSA=YES;“Insulation=EPDM - Ethylene Propylene Diene Monomer”;“Jacket type=Thermoset CPE”;

Not every product uses all the same fields, though many do. That means there is no consistent number of values in every cell. It could be several or 16 or …something else.
Also, I don’t know why it puts quotes around some fo the value pairs but not others. It seems not to do it for short ones. Very odd.

I would like to:

1. Expand the one cell into columns using the value label before the “=” then populate the product row with the custom value that comes after the “=”.
2. Edit values as I see fit.
3. Re-compress all those cells back into one for an import back into my e-commerce system.

Easy, right?

Thanks for reading and considering my case!

Mike

[Bill_French]

Hi Mike, and welcome to the community!

Mike_Sobol:

I don’t know why it puts quotes around some fo the value pairs but not others. It seems not to do it for short ones. Very odd.

No, it seems to do it for ones that have no embedded spaces. :winking_face:

Mike_Sobol:

Easy, right?

No again. :winking_face: You need a complex parameter parser and builder. This is not easily achieved in formulas; ergo - best to think about a scripting process to pull this off - something like this one.

Mike_Sobol:

Expand the one cell into columns using the value label before the “=” then populate the product row with the custom value that comes after the “=”.

And by “populate the product row” you really mean push the values (after 😃 into columns who field names match the names to the left of the = sign? Ergo…

“Number of Conductors=4” needs to be saved to a field named “Number of Conductors” with a value of 4, right?

[Mike_Sobol]

Thanks, Bill. You described my situation more clearly than I did.

Any ideas about how I can get help creating the appropriate script?

[Bill_French]

Mike_Sobol:

how I can get help creating the appropriate script?

Do you have a child under the age of 12? Chances are she knows javascript. :winking_face:

Seriously, though, there are probably kids nearby (highschool, community college, etc) that are javascript programmers and I know many business people who lean on these little techno-savages to build code.

Short of that, hire someone here to build just the basic task and teach you a bit; once you see the pattern, you’ll probably be able to pick it up pretty fast.

Create a new post that describes a cry for a simple field parsing script. There may also be some examples that I’m not aware of.

[Mike_Sobol]

I have people I can ask, but someone already skilled in airtable seems like a good idea. I’ll put up a post. Are you available for hire?

[Bill_French]

Mike_Sobol:

Are you available for hire?

Yes, I am, and appreciate the ask. However, my rate is $250/hr and I’m slammed well beyond Covid-19. You can get this done far cheaper and much sooner.

[Mike_Sobol]

Ok. Thanks, Bill. I’ll put the call out.