在Google表单上进行模糊匹配 [英] Fuzzy match on google sheets

问题描述

我试图模糊匹配谷歌表格中的两列，我尝试了很多公式，但我认为它会归结为一个脚本来帮助。

<我有一个产品ID的列例如

• E20067

另一张有另一列的图片，其中包含与此产品代码相关的图片网址，例如

• 然后使用公式：
  

  / b>
  
  

    = REGEXEXTRACT（B1，JOIN（|，$ A $ 1：$ A $ 3））
    

  它将与ID中的一个匹配。将公式向下拖动以查看结果，如上图所示。

  
  
  

  查看更多 info here

  
  

  I'm trying to fuzzy match two columns in google sheets, i've tried numerous formulas but I think it's going to come down to a script to help out.

  I have a column with product ID's e.g.

  • E20067

  and then I have another sheet with another column which has image url's relating to this product code such as

  • http://wholesale.test.com/product/E20067/web_images/E20067.jpg
  • http://wholesale.test.com/product/E20067/high_res/E20067.jpg
  • http://wholesale.test.com/product/E20067/high_res/E20067-2.jpg

  What I'm wanting to do is "fuzzy" match both of these columns for their product ID, and then create a new column for each match. So it would have the product ID then on the same row in multiple columns each product image URL - like the image below:

  Is there a way to do this in google sheets using a script or a formula?

  解决方案

  In Google sheets there are a few powerful 'regex' formulas.

  Suppose, you have ID list in column A, and URL list in column B

  Then use formula:

  =REGEXEXTRACT(B1,JOIN("|",$A$1:$A$3))
  

  It will match one of ID's. Drag the formula down to see the result as in picture above.

  See more info here

  这篇关于在Google表单上进行模糊匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！