如果字符串包含'Vlookup返回#N/A [英] Vlookup returns #N/A if string contains a '

问题描述

我正在使用vlookup语句: = VLOOKUP(B1232，Sheet1！A:B，2,0).B1232中的单元格包含字符串:

I am using a vlookup statement: =VLOOKUP(B1232,Sheet1!A:B,2,0). The cell in B1232 contains the string:

'You Rawk !!〜'

'You Rawk!!~'

在要查找的字符串中包含'"，该程序将返回#N/A.我相信Vlookup命令在运行搜索时会省略开头的单引号，对吗?有什么办法可以解决此问题，以便我可以运行VLOOKUP?

With the "'" inside the string that I want to go and find, the program retursn #N/A. I believe that the Vlookup command is omitting the opening single-quote when it runs the search, is that true? Is there any way to work around this so that I can run the VLOOKUP?

推荐答案

我认为引号不是问题-Excel使用〜" [代字号]作为转义字符"，因此它的值存在问题包含〜".您可以在VLOOKUP中使用 SUBSTITUTE 函数将〜"替换为"~~"-当使用两个波浪号时，第一个波浪号告诉excel将第二个波浪号视为文字〜"，即use

I don't think the quote is the problem - Excel uses "~" [tilde] as an "escape character" so it has a problem with values that contain "~". You can use a SUBSTITUTE function within your VLOOKUP to replace "~" with "~~" - when using two tildes the first one tells excel to treat the second as a literal "~", i.e. use

= VLOOKUP(SUBSTITUTE(B1232，〜"，"~~")，Sheet1！A:B，2,0)

无论B1232是否包含〜"都可以使用

That will work whether B1232 contains "~" or not

这篇关于如果字符串包含'Vlookup返回#N/A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！