excel - 在excel中展开数据

Question

我想扩展下面的值。任何人都有一个好的公式或流程，我想要的格式是 000.000.000.000，可能是或多或少的句点，例如 000.000.000。

（开始）单元格 A2

1.6.129.5

（公式）单元格 B2

001.006.129.005

*我正在使用 excel 2016

谢谢！

标签： excel

Answer 1

如果您有 Office 365 Excel，则可以使用 TEXTJOIN 作为数组公式：

=TEXTJOIN(".",TRUE,TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),(ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1))-1)*99+1,99)),"000"))

作为一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。

---

如果您没有 Office 365，则需要单独解析每个部分：

=MID(
            IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),1,99))),"." & TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),1,99)),"000"),"") &
            IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),99,99))),"." & TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),99,99)),"000"),"") &
            IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),198,99))),"." & TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),198,99)),"000"),"") &
            IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),297,99))),"." & TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),297,99)),"000"),"") &
            IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),396,99))),"." & TEXT(--TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),396,99)),"000"),"")
,2,99)

您将需要为尽可能多的部件添加一个新的 IF，这个假设部件的最大数量为 5。

---

或者您可以使用此 UDF，它在提供的分隔符上拆分，然后使用提供的数字格式格式化每个部分并将其重新组合在一起：

Function MyFormat(str As String, delim As String, pat As String) As String
Dim strArr() As String
strArr = Split(str, delim)

Dim i As Long
For i = LBound(strArr) To UBound(strArr)
    If IsNumeric(strArr(i)) Then
        strArr(i) = Format(Val(strArr(i)), pat)
    End If
Next i
MyFormat = Join(strArr, delim)
End Function

那么你可以从工作表中使用它：

=MyFormat(A1,".","000")