这个答案是基于你之前的问题和给出的答案。它假设你也有不同的外观样本数据，例如3 inch * 5 in。你可以再次基于正则表达式创建自己的UDF。对于这个例子，我创建了一个'RegexExtract'和'RegexReplace'函数：

Public Function RegexExtract(str As String, pat As String, Optional gFlag As Boolean = False, Optional pos As Integer = 0, Optional cse As Boolean = True) As String

Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")

RE.Pattern = pat
RE.Global = gFlag
RE.IgnoreCase = cse

If RE.Test(str) Then
    RegexExtract = RE.Execute(str)(pos)
Else
    RegexExtract = vbNullString
End If

End Function

Public Function RegexReplace(str As String, pat As String, rep As String, Optional gFlag As Boolean = True, Optional cse As Boolean = True) As String

Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")

RE.Pattern = pat
RE.Global = gFlag
RE.IgnoreCase = cse
RegexReplace = RE.Replace(str, rep)

End Function

我使用以下命令调用函数：

=RegexReplace(RegexExtract(A2,"\d+(?:\.\d+)?(?:''|""|in(?:ch)?\b)?(?:\s*[*x]\s*\d+(?:\.\d+)?(?:''|""|in(?:ch)?\b)?)*\s*[*x]\s*\d+(?:\.\d+)?"),"(\d+(?:\.\d+)?)[^\d.]+","$1x")

这导致：

RegexExtract：

这部分将查看您的输入并基于以下模式提取子字符串：

\d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)?(?:\s*[*x]\s*\d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)?)*\s*[*x]\s*\d+(?:\.\d+)?

查看在线demo

• \d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)?-用可选小数 * 和 * 可选尾随英寸分界匹配数字;
• (?:-打开非捕获组;
• \s*[*x]\s*-在0+空格字符之间匹配星号或文字'x';
• \d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)?-匹配与第一个bullit中相同的图案;
• )*-关闭非捕获组并匹配0+次。
  RegexReplace：

这部分将查看您的输入并基于以下模式替换子字符串：

(\d+(?:\.\d+)?)[^\d.]+

查看在线demo

• (\d+(?:\.\d+)?)-第一个捕获组，用于匹配具有可选小数的数字;
• [^\d.]+- 1+（Greedy）除数字或点以外的字符。

替换为对此第一组的反向引用：

$1x

**注意：**链接将显示不同的模式，以排除示例中的换行符。

使用简单替换：

Function GetNumeric(CellRef As String) As String
    GetNumeric = Replace(CellRef, " ", "")
    GetNumeric = Replace(GetNumeric, """", "")
    GetNumeric = Replace(GetNumeric, "'", "")
    GetNumeric = Replace(GetNumeric, "*", "x")
End Function

这将工作，但是，Option Compare Text将导致问题，如果你有大写的X s在您的样本数据。

Function GetNumeric(CellRef As String)
    Dim include As String, i As Long
    include = "0123456789.x*"
    For i = 1 To Len(CellRef)
        If InStr(1, include, Mid(CellRef, i, 1)) <> 0 Then GetNumeric = GetNumeric & Mid(CellRef, i, 1)
    Next
    GetNumeric = Replace(GetNumeric, "*", "x")
End Function

如果你需要一个可以使用Option Compare Text的函数，你可以用途：

Function GetNumeric2(CellRef As String)
    Dim include As String, i As Long, j As Long
    include = "0123456789.x*"
    For i = 1 To Len(CellRef)
        For j = 1 To Len(include)
            If Asc(Mid(CellRef, i, 1)) = Asc(Mid(include, j, 1)) Then GetNumeric2 = GetNumeric2 & Mid(CellRef, i, 1)
        Next
    Next
    GetNumeric2 = Replace(GetNumeric2, "*", "x")
End Function

最后，一个应该允许2X2的版本：

Function GetNumeric3(CellRef As String)
    Dim include As String, i As Long
    include = "0123456789.x*"
    For i = 1 To Len(CellRef)
        If InStr(1, include, Mid(CellRef, i, 1)) <> 0 Then GetNumeric3 = GetNumeric3 & Mid(CellRef, i, 1)
    Next
    GetNumeric3 = Replace(GetNumeric3, "*", "x")
    For i = 1 To Len(GetNumeric3)
        If Left(GetNumeric3, 1) = "x" Then GetNumeric3 = Mid(GetNumeric3, 2)
        If Right(GetNumeric3, 1) = "x" Then GetNumeric3 = Left(GetNumeric3, Len(GetNumeric3) - 1)
    Next
    GetNumeric3 = LCase(GetNumeric3)
End Function

感谢FunThomas的帮助。
结果：

您可以将此函数与查找表一起使用：

=LET(colLookup,VSTACK(tblLookup,CHAR(SEQUENCE(26,,65))),
step1, REDUCE([@[Current String]],colLookup,
     LAMBDA(a,b,SUBSTITUTE(a,b,""))),
SUBSTITUTE(step1,"x","*"))

CHAR(SEQUENCE(26,,65)创建从A到Z的列表

---更新：VBA解决方案

Public Function replaceExt(t As String) As String

Dim charToReplace As Variant
charToReplace = Array("""", "'", " ")

Dim i As Long
'replace defined characters
For i = 0 To UBound(charToReplace)
    t = Replace(t, charToReplace(i), "")
Next

'now replace all upper charachters ASCII 65 to 90
For i = 65 To 90
    t = Replace(t, Chr(i), "")
Next

'Finally replace lower case x by *
t = Replace(t, "x", "*")
replaceExt = t
End Function