假设您在A列中有一个包含许多产品代码的工作表。这些代码的格式为A4,B12,AD4等。由于公司运营方式的变化,系统会指示您更改所有产品代码,以便在字母和数字之间包含一个破折号。

您可以通过多种方式执行此任务。如果产品代码的结构一致,则插入短划线很容易。

例如,如果总是有单个字母后跟数字,则可以使用如下公式:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

很有可能不会构造数据,这意味着您可以有一个或两个字母,后跟最多三位数字。因此,A4和QD284都是有效的产品代码。在这种情况下,解决方案公式需要更多的创造力。

处理它的一种方法是使用数组公式。请考虑以下公式:

=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")

如果值在A1-A10中,则可以将此公式放入B1,然后将其复制到该列中。由于它是一个数组公式,因此必须通过按Ctrl + Shift + Enter来输入。该公式将找到第一个数字在单元格中的位置,并在其前面插入一个破折号。

举例来说,假设单元格A1包含BR27。公式的最内部部分INDIRECT(“ 1:100”)将文本1:100转换为范围。这样做是为了使插入或删除行不会影响公式。公式的下一部分ROW(INDIRECT(“ 1:100”))实际上创建了一个值1-100的数组:1,2,3,…​,99,100。这用于对单元格中的每个字符起作用。

下一部分MID(A1,ROW(INDIRECT(“ 1:100”)),1)引用字符串中的每个字符。结果是:“ B”,“ R”,“ 2”和“ 7”。将数组乘以1(公式的下一部分)

导致每个单独的字符都转换为数字。如果字符不是数字,则此转换将产生错误。在转换字符串(BR27)的情况下,结果为:#VALUE,#VALUE,2和7。

下一步是将ISERROR函数应用于乘法结果。这会将错误转换为TRUE,将非错误转换为FALSE,从而生成TRUE,TRUE,FALSE和FALSE。 MATCH函数在TRUE和FALSE值的数组中查找FALSE的精确匹配。在此示例中,由于第一个FALSE值位于数组的第三位置,因此MATCH函数返回数字3。至此,我们基本上知道了第一个数字在单元格中的位置。

最后一个函数是REPLACE,它用于将破折号实际插入源字符串中,从第三个字符开始。

如您所知,执行转换的公式可能有些难以理解。对于那些倾向的人,仅创建用户定义的函数可能会更容易。以下宏是一个示例的示例,该宏将在适当的位置返回带有破折号的字符串:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

宏检查原始字符串中的每个字符,并在找到第一个数字字符时在该点插入一个破折号。您可以通过以下方式使用该函数:

=DashIn(A1)

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

链接:/ excelribbon-ExcelTipsMacros [点击此处在新的浏览器标签中打开该特殊页面]。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(10182)适用于Microsoft Excel 2007和2010。您可以在以下旧版Excel菜单界面中找到本技巧的版本:

链接:/ excel-Inserting_Dashes_between_Letters_and_Numbers [在字母和数字之间插入短划线]。