How to extract everything so that only the URL is left


#1

Hi we are looking to upload our airtable store using a bulk uploader however our bulk uploader will not recognize the photos unless its the url only.

We have over 2000 cells so this will take ages unless anybody has a suggestion? we figured it out on google sheets =Arrayformula(RegexExtract(A1:A,“https?://.+[^)]”))

However as you can see some cells have multiple images so we are stuck and this formula does not work in airtable…

Please help

Lee


#2

@Lee_Oliveira - I’d suggest doing this in either google sheets or excel first, with your array/regex formula, then importing the csv to Airtable.

Clean up the csv first, using find or regex formulas to extract your url(s), since it is not easy to clean up the records in Airtable.


#3

Hi Jeremy, yes we did the copy and paste thing in sheets and then just copied it back to airtable. We were hoping to find a solution in airtable as it would have avoided one less step.

Is there a way to avoid getting that mess in airtable?


#4

Hi Lee, as for multiple images, when you’re cleaning up in google sheets you can do a “text to columns” split on the ) character, and then filter which rows have data in those new columns, then treat however you decide to handle (duplicate/delete)… Then modify your regex as needed since it will strip the trailing ).


#5

Hi Tyler, sounds very promising but i am a bit confused, anyway to clarify ?


#6

highlight column a, then data menu --> split text to columns

image
image

looks like google sheets actually keeps the ) so you could use your same regex formula.

from there, you can isolate which photos have more than one image, because there will be info in column B (click B1, data --> create filter --> not blank) (quick note- this assumes that row1 has header titles, if not you’ll want to create one for filters to work, unlike my screenshots)

then depending if you want to upload multiple photos or just one per row, you could either delete the columns you don’t need, or create new rows so there is max 1 image per row, and do some cut & paste as needed.