Excel公式从单元格中获取字符串值并按字母顺序对其字符排序 [英] Excel formula to take string value from cell and sort its characters in alphabetical order

问题描述

您能帮我制作一个Excel公式，该公式从单元格中获取字符串值并按字母顺序对其字符进行排序吗?

Can you please help me to make Excel formula that takes string value from cell and sorts its characters in alphabetical order?

例如.

原始单元格值:" BACR " 排序的字符单元格:" ABCR "

original cell value: 'BACR' sorted characters cell: 'ABCR'

推荐答案

此UDF将按字符对数字和文本进行排序:

This UDF will sort numbers and Text character by character:

Function sortletter(rng As Range)
    If rng.Count > 1 Then Exit Function
    Dim srtArr() As String
    Dim i&, j&, k&
    ReDim srtArr(1 To Len(rng))
    srtArr(1) = Mid(rng, 1, 1)
    For i = 2 To UBound(srtArr)
        For j = 1 To UBound(srtArr)
            If srtArr(j) = "" Then
                srtArr(j) = Mid(rng, i, 1)
                Exit For
            ElseIf IIf(Asc(Mid(rng, i, 1)) > 96, Asc(Mid(rng, i, 1)) - 32, Asc(Mid(rng, i, 1))) <= IIf(Asc(srtArr(j)) > 96, Asc(srtArr(j)) - 32, Asc(srtArr(j))) Then
                For k = UBound(srtArr) To j + 1 Step -1
                    srtArr(k) = srtArr(k - 1)
                Next k
                srtArr(j) = Mid(rng, i, 1)
                Exit For
            End If
        Next j
    Next i
    sortletter = Join(srtArr, "")
End Function

这是工作簿附带的模块，不在工作表或ThisWorkbook代码中.

Put this is a module attached to the workbook, NOT in the worksheet or ThisWorkbook code.

然后可以像其他任何公式一样调用

Then it can be called like any other formula

=sortletter(A1)

这篇关于Excel公式从单元格中获取字符串值并按字母顺序对其字符排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！