☰

如何让 Excel 在从另一个单元格获取数据时忽略特殊字符和空格？

tag-icon • tag-icon microsoft-excel worksheet-function vba
如何让 Excel 在从另一个单元格获取数据时忽略特殊字符和空格？

我有一个 Excel 工作簿，用于为客户生成报价单。它会根据销售人员的姓名、收到报价单的公司名称、日期和发行编号自动生成唯一的报价单编号（如果同一销售人员在同一天为同一家公司制作两个单独的报价单，则第一个报价单的发行编号为 01，第二个报价单的发行编号为 02，依此类推）

填充了虚拟信息的典型报价号将如下所示：

JS-ABC-05052016-01

'JS' 是销售人员的姓名首字母（John Smith）。'ABC' 是公司名称的前三个字符（ABCompany）。'05052016' 是今天的日期，'01' 是发行号。这些都是销售人员输入的信息。

生成并显示报价编号的单元格中使用的公式是：

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(D11,3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

上述公式获取单元格中输入的信息，并对其进行编译以生成报价编号。

但是，如果公司名称的前三个字符包含空格或特殊字符，则会出现问题。例如，公司名称“AB Company”将生成以下报价单编号：

JS-A.-05052016-01

另一个例子是，公司名称为“A＆B公司”将生成以下报价编号：

JS-A&B-05052016-01

在报价过程中，工作簿将被重命名以包含报价编号。如果报价编号包含特殊字符（例如句点），则可能会导致问题（例如，这可能会弄乱文件类型）。

有没有办法让 Excel 忽略公司名称中任何非字母或数字的字符（包括空格）？例如，让名为“A. & B. Company”的公司生成报价单编号：

JS-ABC-05052016-01

答案1

我想我已经为你找到了答案

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|$&"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Alt+F11——插入——模块——粘贴。

如果愿意，您可以向数组中添加更多特殊字符。这里：sSpecialChars = "\/:*?""<>|$&"

然后在你的单元格中输入：=SUBSTITUTE(removeSpecial(A1)," ","")

结果a &b.company是ab.company

感谢作者，我刚刚发现它。

那么你的公式应该是：

=UPPER(LEFT(SUBSTITUTE(removeSpecial(P4)," ",""),1)&LEFT(SUBSTITUTE(removeSpecial(P5)," ",""),1)&"-"&LEFT(SUBSTITUTE(removeSpecial(D11)," ",""),3)&"-"&LEFT(SUBSTITUTE(removeSpecial(K6)," ",""),2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),4,2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),7,4)&"-"&SUBSTITUTE(removeSpecial(D5)," ",""))

答案2

有点痛苦，但是，=LEFT(D11,3)用你的函数替换

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)

所以就是

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

搜索.，空白和&- 如果存在则添加其他内容。

相关内容