I have a excel sheet where I have columns that say "I like TSX but I prefer 350" "I like beamer but I am fan of S-Class"
我有一张excel表,我的栏目上写着“我喜欢TSX,但我更喜欢350”“我喜欢投影,但我喜欢S级”
I want to replace this with "I like Acura but I prefer BMW" "I like BMW but I am fan of Mercedes"
我想用“我喜欢讴歌,但我更喜欢宝马”取而代之的是“我喜欢宝马,但我是梅赛德斯的粉丝”
I have another excel sheet with the match and replace values. For example TSX, Acura TL, Acura 350 BMW Beamer, BMW
我有另一个带有匹配和替换值的excel表。例如TSX,Acura TL,Acura 350 BMW Beamer,BMW
etc.
I know how to use vlookup, but this is way advance for me. Any help? Thanks you.
我知道如何使用vlookup,但这对我来说是先进的。有帮助吗?谢谢。
1 个解决方案
#1
0
Rather than use vlookup at all, if you don't have many types of cars you could simply use SUBSTITUTE. Works as
如果你没有很多类型的汽车,你可以简单地使用SUBSTITUTE,而不是使用vlookup。作为
=SUBSTITUTE(A1, "TSX", "ACURA")
Then wrap it many times, once for each possibility:
然后将它包裹多次,每种可能性一次:
=SUBSTITUTE(SUBSTITUTE(A1, "TSX", "ACURA"),"S-CLASS", "MERCEDES"))
= SUBSTITUTE(SUBSTITUTE(A1,“TSX”,“ACURA”),“S-CLASS”,“MERCEDES”))
If you have many possibilities, this will either need 2 helper columns, or vba. Let me know if that's the case. Basically without vba you will need to search for the first term in 1 column, then put the 2nd term in the second formula, then replace the 1st term with a vlookup'd equivilent, then replace the 2nd term with a vlookuo'd equivilent (using substitute as above).
如果您有很多可能性,这将需要2个辅助列或vba。如果是这样,请告诉我。基本上没有vba你需要在1列中搜索第一个术语,然后将第二个术语放在第二个公式中,然后用vlookup'd equivilent替换第一个术语,然后用vlookuo'd equivilent替换第二个术语(使用上面的替代品)。
Edit
Reading the q again it seems there are many possibilities, so we will need to use two helper columns and areay formulas to solve. VBA is likely better, but In on my phone so cant error test anything, and you might not want vba anyway.
再次阅读q似乎有很多可能性,所以我们需要使用两个辅助列和areay公式来解决。 VBA可能更好,但在我的手机上,因此无法测试任何错误,你可能也不想要vba。
The principle for these helper columns is going to be to find which 2 vehicles exist in the text. Once you know which two exist, you can simply replace both as above.
这些辅助列的原理是找到文本中存在哪两种车辆。一旦你知道哪两个存在,你可以简单地替换上面两个。
The first stage of the formula we will be putting in colns B and C, (assuming your 'vlookup text' is in columns A and B on sheet2 from rows 1 to 100) is:
我们将在colns B和C中放入公式的第一阶段(假设你的'vlookup文本'在第1行到第100行的sheet2上的A列和B列中)是:
=If(iserror(search($A1,sheet2!$A$1:$A100)),"",row(sheet2!$A$1:$A$100))
This is an array formula, which works on each cell in the range you give it. To confirm it, press CTRL + SHIFT + ENTER instead of just ENTER. It gives you an array of results, that you need to collapse into a single answer. so in column B, wrap it in a MIN function. For column C, wrap it in a Max function.
这是一个数组公式,适用于您提供的范围内的每个单元格。要确认它,请按CTRL + SHIFT + ENTER而不是ENTER。它为您提供了一系列结果,您需要将其合并为一个答案。所以在B列中,将其包装在MIN函数中。对于C列,将其包装在Max函数中。
So now column B shows the row number of 1 matching term, and row C shows the row number of the 2nd matching term.
所以现在B列显示1个匹配项的行号,C行显示第2个匹配项的行号。
So now in column D, we know the row number of the first match, the first replacement term, and thend match and replacement term. Replace everything as follows:
所以现在在D列中,我们知道第一个匹配的行号,第一个替换术语,以及匹配和替换术语。替换所有内容如下:
=Substitute(substitute(A1,INDEX(SHEET2!$A$1:$A$100, B1),INDEX(SHEET2!$B$1:$B$100, C1)),INDEX(SHEET2!$A$1:$A$100, C1),INDEX(SHEET2!$B$1:$B$100, B1)))
Make sure you confirm with CTRL SHIFT ENTER instead of just enter.
确保使用CTRL SHIFT ENTER确认,而不是仅输入。
Apologies if this isnt formatted 100%; Im on my phone and will error check it later if it causes problems. Let me know if you have questions about how this works.
抱歉,如果这不是100%格式化;我在手机上,如果它导致问题,将在以后检查错误。如果您对此如何运作有疑问,请与我们联系。
#1
0
Rather than use vlookup at all, if you don't have many types of cars you could simply use SUBSTITUTE. Works as
如果你没有很多类型的汽车,你可以简单地使用SUBSTITUTE,而不是使用vlookup。作为
=SUBSTITUTE(A1, "TSX", "ACURA")
Then wrap it many times, once for each possibility:
然后将它包裹多次,每种可能性一次:
=SUBSTITUTE(SUBSTITUTE(A1, "TSX", "ACURA"),"S-CLASS", "MERCEDES"))
= SUBSTITUTE(SUBSTITUTE(A1,“TSX”,“ACURA”),“S-CLASS”,“MERCEDES”))
If you have many possibilities, this will either need 2 helper columns, or vba. Let me know if that's the case. Basically without vba you will need to search for the first term in 1 column, then put the 2nd term in the second formula, then replace the 1st term with a vlookup'd equivilent, then replace the 2nd term with a vlookuo'd equivilent (using substitute as above).
如果您有很多可能性,这将需要2个辅助列或vba。如果是这样,请告诉我。基本上没有vba你需要在1列中搜索第一个术语,然后将第二个术语放在第二个公式中,然后用vlookup'd equivilent替换第一个术语,然后用vlookuo'd equivilent替换第二个术语(使用上面的替代品)。
Edit
Reading the q again it seems there are many possibilities, so we will need to use two helper columns and areay formulas to solve. VBA is likely better, but In on my phone so cant error test anything, and you might not want vba anyway.
再次阅读q似乎有很多可能性,所以我们需要使用两个辅助列和areay公式来解决。 VBA可能更好,但在我的手机上,因此无法测试任何错误,你可能也不想要vba。
The principle for these helper columns is going to be to find which 2 vehicles exist in the text. Once you know which two exist, you can simply replace both as above.
这些辅助列的原理是找到文本中存在哪两种车辆。一旦你知道哪两个存在,你可以简单地替换上面两个。
The first stage of the formula we will be putting in colns B and C, (assuming your 'vlookup text' is in columns A and B on sheet2 from rows 1 to 100) is:
我们将在colns B和C中放入公式的第一阶段(假设你的'vlookup文本'在第1行到第100行的sheet2上的A列和B列中)是:
=If(iserror(search($A1,sheet2!$A$1:$A100)),"",row(sheet2!$A$1:$A$100))
This is an array formula, which works on each cell in the range you give it. To confirm it, press CTRL + SHIFT + ENTER instead of just ENTER. It gives you an array of results, that you need to collapse into a single answer. so in column B, wrap it in a MIN function. For column C, wrap it in a Max function.
这是一个数组公式,适用于您提供的范围内的每个单元格。要确认它,请按CTRL + SHIFT + ENTER而不是ENTER。它为您提供了一系列结果,您需要将其合并为一个答案。所以在B列中,将其包装在MIN函数中。对于C列,将其包装在Max函数中。
So now column B shows the row number of 1 matching term, and row C shows the row number of the 2nd matching term.
所以现在B列显示1个匹配项的行号,C行显示第2个匹配项的行号。
So now in column D, we know the row number of the first match, the first replacement term, and thend match and replacement term. Replace everything as follows:
所以现在在D列中,我们知道第一个匹配的行号,第一个替换术语,以及匹配和替换术语。替换所有内容如下:
=Substitute(substitute(A1,INDEX(SHEET2!$A$1:$A$100, B1),INDEX(SHEET2!$B$1:$B$100, C1)),INDEX(SHEET2!$A$1:$A$100, C1),INDEX(SHEET2!$B$1:$B$100, B1)))
Make sure you confirm with CTRL SHIFT ENTER instead of just enter.
确保使用CTRL SHIFT ENTER确认,而不是仅输入。
Apologies if this isnt formatted 100%; Im on my phone and will error check it later if it causes problems. Let me know if you have questions about how this works.
抱歉,如果这不是100%格式化;我在手机上,如果它导致问题,将在以后检查错误。如果您对此如何运作有疑问,请与我们联系。