消除除字母以外的所有字符的公式 [英] Formula to eliminate all but alpha characters

问题描述

我需要在Excel中清理一列名称，以消除所有非Alpha字符，包括句点，逗号，空格，连字符和撇号.

I need to scrub a column of names in Excel to eliminate all non-Alpha characters including periods, commas, spaces, hyphens and apostrophes.

示例: 将O'Malley-Smith, Tom, Jr.更改为OMALLEYSMITHTOMJR

客户端要求这是一个Excel函数，否则我将使用类似于replaceAll("[^a-zA-Z]", "").toUpperCase()的快速Java程序来使其变得容易.我似乎找不到任何看起来像现成功能的东西来完成整个SUBSTITUTE函数的混乱—似乎每个单元只能使用一个.

The client requires this to be an Excel function, otherwise I'd make it easy with a quick Java program similar to replaceAll("[^a-zA-Z]", "").toUpperCase(). I cannot seem to find anything that looks like an off-the-shelf function to do this outside of a whole mess of SUBSTITUTE functions - which only seem to be available one-per-cell.

如果我需要的话，我不太会开发自定义宏.

I'm not terribly fluent with developing custom macros if that's what I need.

推荐答案

前段时间，我也有类似的需求，并且发现了行之有效的方法.

I had a similar need sometime ago and found something that worked great.

按Alt + F11打开Visual Basic编辑器.插入一个新的模块并粘贴以下代码.

Press Alt+F11 to open the Visual Basic editor. Insert a new Module and paste the following code.

Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(UCase(Rng), n, 1))
            Case 48 To 57, 65 To 90
                strTemp = strTemp & Mid(UCase(Rng), n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

CleanCode现在是新功能，您可以将其用作公式.

CleanCode now is new function and you can use it as a formula.

因此，在要操作的字符串所在的单元格旁边，只需复制=CleanCode(yourcell)

So next to the cell with the string you want to manipulate just copy =CleanCode(yourcell)

这篇关于消除除字母以外的所有字符的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！