vba - Excel ProperCase 到单词

Question

有没有一种快速的方法(或任何方法)将正确的大小写文本转换为单个单词。我有几千行行，并且认为询问 SO 比手动拆分文本更好。谷歌帮不上忙

例如""EffectiveFromDate应该翻译成"Effective From Date"

Answer 1

试试这个 UDF:

Function ProperCaseToWords(x As String) As String
    Dim i As Byte
    'A=65, Z=90
    For i = 65 To 90
        x = Replace(x, Chr(i), " " & Chr(i))
    Next i
    ProperCaseToWords = Trim(x)
End Function

然后在任何单元格中调用它:=ProperCaseToWords(A1)

或者从 Sub 调用它:

Dim cell As Range
For Each cell In Range("A1:A100")
    cell.Value = ProperCaseToWords(cell.Value)
Next