如何用SUBSTITUTE公式在Excel中替换引号？ [英] How can I substitute quotation marks in Excel with SUBSTITUTE formula?

问题描述

我有工作表，我需要命名范围来对应另一个单元格的内容。单元格中的文本类似于：

 分区w Studs 16oc 
 命名范围不能有空格，或者最重要的是。的特殊字符。所以，范围命名如下：
  PartitionswStuds16oc 
  
要将前者更改为对工作表的引用，我可以使用以下公式来处理这些空格：
  = SUBSTITUTE（B1，，）
  
但是，我不能用替换，因为双引号用于指定公式中的文本。Excel无法按预期解析以下公式： 
  = SUBSTITUTE（SUBSTITUTE（B1，，），，）
  
有关如何解决这个问题的任何提示？我知道我可以将文本改为 16-in。而不是 16，但我想保留如可能，我的客户要求。
解决方案
 一个：
  = SUBSTITUTE（SUBSTITUTE（B1， ），，）
  
 
I have worksheet where I need named ranges to correspond to the contents of another cell.  The text in the cell is something like:
Partitions w Studs 16" oc
Named ranges cannot have spaces, or most importantly, special characters like ".  So, the range is named the following:
PartitionswStuds16oc
To change the former into a reference to the latter in the worksheet, I can handle removing the spaces with the following formula:
=SUBSTITUTE(B1," ","")
I cannot, however, substitute the " because the double-quotation mark is used to specify text in the formula.  Excel can't parse the following formula, as expected:
=SUBSTITUTE(SUBSTITUTE(B1," ",""),""","")
Any tips on how to get around this?  I know I could change the text to say 16-in. instead of 16", but I want to keep it as my client requested if possible.
解决方案
"""" escapes a ":
=SUBSTITUTE(SUBSTITUTE(B1," ",""), """", "")


                        
这篇关于如何用SUBSTITUTE公式在Excel中替换引号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！