如何从给定字符串中提取相乘的数字？

huangapple
go评论88阅读模式
英文:

How to extract the multiplied numbers from the given string?

问题

需要从给定的字符串中提取相乘的数字。乘法参数（作为字符串）可以是 "x" 或 "*"（星号符号）。
数字本身可能包含（或不包含）英寸符号（双引号 "）。
可能在乘法参数和数字本身之间有空格（或没有）。
我已尝试下面的函数，但它提取字符串中的所有数字并将它们组合在一起。
不区分大小写很重要，因为我始终在所有宏上使用 Option Compare Text。

英文:

I need to extract the multiplied numbers from the given strings.
The multiplication parameter (as a string) is either "x" or "*" (asterisk sign).
The numbers itself may contain (or not) the inch sign (double quotes ").
There is a white space may be found (or not) between multiplication parameter and numbers itself.
I have tried the below function, But it extracts all numbers from string and combine them.
also case sensitivity is not important as I am always using Option Compare Text on all my macros.

Current String Expected Result
XX 2" * 3" RRR 2x3
BBB 2"*3" HHH 2x3
MMMM 2*3*5 FF EE 2x3x5
RTE 2*3 EE XX 2x3
AAA 4.5 x 5'' ERT EE 4.5x5
XX 4''x5'' XX XX 4x5
WWW 4''x 3.5  WWW 4x3.5
EEE 4*5 NN 4x5 
  1. Function GetNumeric(CellRef As String)
  2.     Dim StringLength As Long, i As Long, Result As Variant
  3.     StringLength = Len(CellRef)
  4.     For i = 1 To StringLength
  5.       If IsNumeric(Mid(CellRef, i, 1)) Then
  6.          Result = Result & Mid(CellRef, i, 1)
  7.       End If
  8.     Next i
  9.     GetNumeric = Result
  10. End Function

答案1

得分: 3

这个答案基于你之前的问题和那些问题的答案。它假设你可能有不同样式的示例数据，比如3 inch * 5 in。你可以再次基于正则表达式创建自己的UDF。对于这种情况，我创建了一个名为 'RegexExtract' 和 'RegexReplace' 的函数：

  1. 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
  3. Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
  5. RE.Pattern = pat
  6. RE.Global = gFlag
  7. RE.IgnoreCase = cse
  9. If RE.Test(str) Then
  10.     RegexExtract = RE.Execute(str)(pos)
  11. Else
  12.     RegexExtract = vbNullString
  13. End If
  15. End Function
  17. Public Function RegexReplace(str As String, pat As String, rep As String, Optional gFlag As Boolean = True, Optional cse As Boolean = True) As String
  19. Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
  21. RE.Pattern = pat
  22. RE.Global = gFlag
  23. RE.IgnoreCase = cse
  24. RegexReplace = RE.Replace(str, rep)
  26. End Function

我使用以下方式调用这些函数：

  1. =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")

这将得到如下结果：

如何从给定字符串中提取相乘的数字？

英文:

This answer is based on your previous questions and the answers given to those. It assumes that you would also have different looking sample data like 3 inch * 5 in for example. You could again create your own UDF based on a regular expression. For this case I created an 'RegexExtract' and 'RegexReplace' function:

  1. 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
  3. Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
  5. RE.Pattern = pat
  6. RE.Global = gFlag
  7. RE.IgnoreCase = cse
  9. If RE.Test(str) Then
  10.     RegexExtract = RE.Execute(str)(pos)
  11. Else
  12.     RegexExtract = vbNullString
  13. End If
  15. End Function
  17. Public Function RegexReplace(str As String, pat As String, rep As String, Optional gFlag As Boolean = True, Optional cse As Boolean = True) As String
  19. Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
  21. RE.Pattern = pat
  22. RE.Global = gFlag
  23. RE.IgnoreCase = cse
  24. RegexReplace = RE.Replace(str, rep)
  26. End Function

I called the functions using:

  1. =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")

This results in:

如何从给定字符串中提取相乘的数字？

RegexExtract:

This part would look at your input and extract a substring based on this pattern:

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

See an online demo

  • \d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)? - Match digits with optional decimals and optional trailing inch demarcation;
  • (?: - Open a non-capture group;
    • \s*[*x]\s* - Match an asterisk or literal 'x' between 0+ whitespace chars;
    • \d+(?:\.\d+)?(?:''|"|in(?:ch)?\b)? - Match the same pattern as in the 1st bullit;
    • )* - Close the non-capture group and match it 0+ times.

RegexReplace:

This part would look at your input and replace a substring based on this pattern:

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

See an online demo

  • (\d+(?:\.\d+)?) - A 1st capture group to match digits with optional decimals;
  • [^\d.]+ - 1+ (Greedy) characters other than digit or dot.

Replace with a backreference to this 1st group:

  1. $1x

Note: The link will show a different pattern to exclude newline chars from the samples.

答案2

得分: 2

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

英文:

Use simple replace:

  1. Function GetNumeric(CellRef As String) As String
  2.     GetNumeric = Replace(CellRef, " ", "")
  3.     GetNumeric = Replace(GetNumeric, """", "")
  4.     GetNumeric = Replace(GetNumeric, "'", "")
  5.     GetNumeric = Replace(GetNumeric, "*", "x")
  6. End Function

答案3

得分: 2

这会起作用，但是如果示例数据中有大写的 X，Option Compare Text 会导致问题。

  1.     函数 GetNumeric(CellRef As String)
  2.         Dim include As String, i As Long
  3.         include = "0123456789.x*"
  4.         对 i = 1 到 Len(CellRef) 循环
  5.             如果 InStr(1, include, Mid(CellRef, i, 1)) <> 0 Then GetNumeric = GetNumeric & Mid(CellRef, i, 1)
  6.         下一
  7.         GetNumeric = Replace(GetNumeric, "*", "x")
  8.     结束 函数
  11.     如果需要与 `Option Compare Text` 一起使用的函数，可以使用：
  13.     函数 GetNumeric2(CellRef As String)
  14.         Dim include As String, i As Long, j As Long
  15.         include = "0123456789.x*"
  16.         对 i = 1 到 Len(CellRef) 循环
  17.             对 j = 1 到 Len(include) 循环
  18.                 如果 Asc(Mid(CellRef, i, 1)) = Asc(Mid(include, j, 1)) Then GetNumeric2 = GetNumeric2 & Mid(CellRef, i, 1)
  19.             下一
  20.         下一
  21.         GetNumeric2 = Replace(GetNumeric2, "*", "x")
  22.     结束 函数
  24.     最后，一个应该允许 `2X2` 的版本：
  26.     函数 GetNumeric3(CellRef As String)
  27.         Dim include As String, i As Long
  28.         include = "0123456789.x*"
  29.         对 i = 1 到 Len(CellRef) 循环
  30.             如果 InStr(1, include, Mid(CellRef, i, 1)) <> 0 Then GetNumeric3 = GetNumeric3 & Mid(CellRef, i, 1)
  31.         下一
  32.         GetNumeric3 = Replace(GetNumeric3, "*", "x")
  33.         对 i = 1 到 Len(GetNumeric3) 循环
  34.             如果 Left(GetNumeric3, 1) = "x" Then GetNumeric3 = Mid(GetNumeric3, 2)
  35.             如果 Right(GetNumeric3, 1) = "x" Then GetNumeric3 = Left(GetNumeric3, Len(GetNumeric3) - 1)
  36.         下一
  37.         GetNumeric3 = LCase(GetNumeric3)
  38.     结束 函数
  40. 感谢 FunThomas 的协助。
  42. 结果：
  44. [![enter image description here][1]][1]
英文:

This would work, however, Option Compare Text is going to cause problems if you have uppercase Xs in your sample data..

  1. Function GetNumeric(CellRef As String)
  2.     Dim include As String, i As Long
  3.     include = &quot;0123456789.x*&quot;
  4.     For i = 1 To Len(CellRef)
  5.         If InStr(1, include, Mid(CellRef, i, 1)) &lt;&gt; 0 Then GetNumeric = GetNumeric &amp; Mid(CellRef, i, 1)
  6.     Next
  7.     GetNumeric = Replace(GetNumeric, &quot;*&quot;, &quot;x&quot;)
  8. End Function

If you need a function that works with Option Compare Text, you can use:

  1. Function GetNumeric2(CellRef As String)
  2.     Dim include As String, i As Long, j As Long
  3.     include = &quot;0123456789.x*&quot;
  4.     For i = 1 To Len(CellRef)
  5.         For j = 1 To Len(include)
  6.             If Asc(Mid(CellRef, i, 1)) = Asc(Mid(include, j, 1)) Then GetNumeric2 = GetNumeric2 &amp; Mid(CellRef, i, 1)
  7.         Next
  8.     Next
  9.     GetNumeric2 = Replace(GetNumeric2, &quot;*&quot;, &quot;x&quot;)
  10. End Function

Finally, a version that should allow for 2X2:

  1. Function GetNumeric3(CellRef As String)
  2.     Dim include As String, i As Long
  3.     include = &quot;0123456789.x*&quot;
  4.     For i = 1 To Len(CellRef)
  5.         If InStr(1, include, Mid(CellRef, i, 1)) &lt;&gt; 0 Then GetNumeric3 = GetNumeric3 &amp; Mid(CellRef, i, 1)
  6.     Next
  7.     GetNumeric3 = Replace(GetNumeric3, &quot;*&quot;, &quot;x&quot;)
  8.     For i = 1 To Len(GetNumeric3)
  9.         If Left(GetNumeric3, 1) = &quot;x&quot; Then GetNumeric3 = Mid(GetNumeric3, 2)
  10.         If Right(GetNumeric3, 1) = &quot;x&quot; Then GetNumeric3 = Left(GetNumeric3, Len(GetNumeric3) - 1)
  11.     Next
  12.     GetNumeric3 = LCase(GetNumeric3)
  13. End Function

Thanks to FunThomas for the assist.

Result:

如何从给定字符串中提取相乘的数字？

答案4

得分: 1

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

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

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

如何从给定字符串中提取相乘的数字？

--- 更新：VBA解决方案

  1. Public Function replaceExt(t As String) As String
  3. Dim charToReplace As Variant
  4. charToReplace = Array(""""", "'", " ")
  6. Dim i As Long
  7. 'replace defined characters
  8. For i = 0 To UBound(charToReplace)
  9.     t = Replace(t, charToReplace(i), "")
  10. Next
  12. 'now replace all upper charachters ASCII 65 to 90
  13. For i = 65 To 90
  14.     t = Replace(t, Chr(i), "")
  15. Next
  17. 'Finally replace lower case x by *
  18. t = Replace(t, "x", "*")
  19. replaceExt = t
  20. End Function
英文:

You can use this function together with a lookup table:

  1. =LET(colLookup,VSTACK(tblLookup,CHAR(SEQUENCE(26,,65))),
  2. step1, REDUCE([@[Current String]],colLookup,
  3.      LAMBDA(a,b,SUBSTITUTE(a,b,&quot;&quot;))),
  4. SUBSTITUTE(step1,&quot;x&quot;,&quot;*&quot;))

CHAR(SEQUENCE(26,,65) creates a list from A to Z

如何从给定字符串中提取相乘的数字？

--- Update: VBA solution

  1. Public Function replaceExt(t As String) As String
  3. Dim charToReplace As Variant
  4. charToReplace = Array(&quot;&quot;&quot;&quot;, &quot;&#39;&quot;, &quot; &quot;)
  6. Dim i As Long
  7. &#39;replace defined characters
  8. For i = 0 To UBound(charToReplace)
  9.     t = Replace(t, charToReplace(i), &quot;&quot;)
  10. Next
  12. &#39;now replace all upper charachters ASCII 65 to 90
  13. For i = 65 To 90
  14.     t = Replace(t, Chr(i), &quot;&quot;)
  15. Next
  17. &#39;Finally replace lower case x by *
  18. t = Replace(t, &quot;x&quot;, &quot;*&quot;)
  19. replaceExt = t
  20. End Function

huangapple
  • 本文由 发表于 2023年4月13日 15:17:21
  • 转载请务必保留本文链接：https://go.coder-hub.com/76002646.html
  • excel
  • regex
  • text-extraction
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定