正则表达式 - 在字符串内匹配(Regular expression - matching inside a string)
我一直试图找到一个正则表达式来匹配下面表达式中的数字,但我需要某个组。
例如:
Vdsl 1.10.4
怎么可能匹配10
我还需要一个匹配4的表达式
PS我正在使用正则表达式Oracle SQL函数regex_substr()
谢谢
编辑:
我想我找到了一个解决方法:
使用REGEXP_SUBSTR(),您可以将其与点匹配:'。10。' 之后使用REGEXP_REPLACE可以提取数字。
REGEXP_REPLACE(REGEXP_SUBSTR('Vdsl 1.10.4','。\ d +。'),'。(\ d +)。','\ 1')
并为最后一个号码
REGEXP_REPLACE(REGEXP_SUBSTR('Vdsl 1.10.4','。\ d + $'),'。(\ d +)','\ 1')
I have been trying to find a regular expression for matching just numbers in the following expression but I need certain group.
For example:
Vdsl 1.10.4
How it's possible to match 10
Also I need an expression for matching 4
P.S I'm using as Regex flavour Oracle SQL function regex_substr()
Thanks
Edit:
I think I found a workaround:
with REGEXP_SUBSTR() you can match it with dots: '.10.' Afterwards with REGEXP_REPLACE the number can be extracted.
REGEXP_REPLACE(REGEXP_SUBSTR('Vdsl 1.10.4','.\d+.'), '.(\d+).', '\1')
and for the last number
REGEXP_REPLACE(REGEXP_SUBSTR('Vdsl 1.10.4','.\d+$'), '.(\d+)', '\1')
原文:https://stackoverflow.com/questions/31359617
最满意答案
快速和简单的答案,我过滤,以获得独特的组合,只有粘贴作为价值观,然后单位我对产品sumif对一张桌子得到单位,然后在它旁边的单元(销售)我做了一个sumif反对产品组合上的第二张表。
虽然在VBA中完成
然后运行一些代码,检查两个单位和销售额是否均等于0(每个单元格旁边都是0),然后移除整个行。 如果有人想看代码。 让我知道,我会发布。
第一步是创建一个包含所有组合的用户界面,并仅复制/粘贴唯一值....
Columns("A:A").Select Application.CutCopyMode = False Selection.insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "UI" Range("A2").Select ActiveCell.FormulaR1C1 = _ "=RC[1]&"",""&RC[2]&"",""&RC[3] &"",""&RC[4]&"",""&RC[5]&"",""&RC[6]&"",""&RC[7]&"",""&RC[8]" Selection.AutoFill Destination:=Range("A2:A27912") Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range("A1:A30000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "O1"), Unique:=True Range("O1").Select Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
接下来的事情是在列旁边添加产品,然后在“文本到列”中添加一个对销售额和销售额旁边的总和。
Selection.Copy Sheets("Next").Select Range("A1").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True Sheets("Other").Select Range("B1:L1").Select Selection.Copy Sheets("Next").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Range("L1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.349986266670736 .PatternTintAndShade = 0 End With ActiveCell.FormulaR1C1 = "UI" Range("L2").Select Sheets("Other").Select Range("O2").Select Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select Selection.Copy Sheets("Next").Select ActiveSheet.Paste Range("J2").Select Application.CutCopyMode = False Range("J2").Select ActiveCell.FormulaR1C1 = _ "=SUMIF('Other'!R2C1:R50000C1,RC12,'Other'!R2C[1]:R50000C[1])" Range("J2").Select Selection.AutoFill Destination:=Range("J2:K2"), Type:=xlFillDefault Range("J2:K2").Select Selection.AutoFill Destination:=Range("J2:K10000") Range("J2:K10000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("L:L").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft
(这部分代码不是非常整洁,但它有诀窍...)
接下来我删除了0,如果两个值都是0 ...
Dim VRange As Range Set VRange = Range(ActiveSheet.Range("J1"), ActiveSheet.Range("J1").End(xlDown)) With VRange .AutoFilter .AutoFilter Field:=1, Criteria1:="0" .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilter End With Sheets("Other").Select Range("A1:I1").ClearContents Range("A1:I1").Value = Sheets("Data").Range("C1:K1").Value Dim URange As Range Set URange = Range(ActiveSheet.Range("J1"), ActiveSheet.Range("J1").End(xlDown)) With URange .AutoFilter .AutoFilter Field:=1, Criteria1:="0" .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilter End With
请注意,我已对此进行了一些更改,请参考您自己的数据并与之匹配...
希望这是正确的所有代码:P有更多的涉及我可能复制了错误的代码:(
The quick and easy answer, I filtered to get unique combinations only pasted as values and then for Units I did a sumif on the product against one table to get units and then in the cell next to it (sales) I did a sumif against the second table on the product combination.
Though done in VBA
Then ran some code that checked if both units and sales was = 0 (0 in each cell next to another) then removed entirerow. If anyone wants to see the code. Let me know and I'll post.
The first step was to create a UI with all combinations and copy/paste unique values only....
Columns("A:A").Select Application.CutCopyMode = False Selection.insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "UI" Range("A2").Select ActiveCell.FormulaR1C1 = _ "=RC[1]&"",""&RC[2]&"",""&RC[3] &"",""&RC[4]&"",""&RC[5]&"",""&RC[6]&"",""&RC[7]&"",""&RC[8]" Selection.AutoFill Destination:=Range("A2:A27912") Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range("A1:A30000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "O1"), Unique:=True Range("O1").Select Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
The next thing was to sum products next to the columns and add in the "text to columns" So baically a sumif against the UI for sales and next to it for quantity...
Selection.Copy Sheets("Next").Select Range("A1").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True Sheets("Other").Select Range("B1:L1").Select Selection.Copy Sheets("Next").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Range("L1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.349986266670736 .PatternTintAndShade = 0 End With ActiveCell.FormulaR1C1 = "UI" Range("L2").Select Sheets("Other").Select Range("O2").Select Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select Selection.Copy Sheets("Next").Select ActiveSheet.Paste Range("J2").Select Application.CutCopyMode = False Range("J2").Select ActiveCell.FormulaR1C1 = _ "=SUMIF('Other'!R2C1:R50000C1,RC12,'Other'!R2C[1]:R50000C[1])" Range("J2").Select Selection.AutoFill Destination:=Range("J2:K2"), Type:=xlFillDefault Range("J2:K2").Select Selection.AutoFill Destination:=Range("J2:K10000") Range("J2:K10000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("L:L").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft
(This section of the code isn't very neat but it does the trick...)
Next I removed the 0s if both values are 0s...
Dim VRange As Range Set VRange = Range(ActiveSheet.Range("J1"), ActiveSheet.Range("J1").End(xlDown)) With VRange .AutoFilter .AutoFilter Field:=1, Criteria1:="0" .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilter End With Sheets("Other").Select Range("A1:I1").ClearContents Range("A1:I1").Value = Sheets("Data").Range("C1:K1").Value Dim URange As Range Set URange = Range(ActiveSheet.Range("J1"), ActiveSheet.Range("J1").End(xlDown)) With URange .AutoFilter .AutoFilter Field:=1, Criteria1:="0" .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilter End With
Please note that I have made some changes to this so refer to your own data and match it...
Hope this is all the code for the right thing :P There is a lot more involved any I may have copied over the wrong code :(
相关问答
更多-
你收到错误了吗? 我认为你的pasteSheet.Cells(...行可能会抛出错误。看看你如何在.Cells()之前添加工作表名称?你应该总是用Rows()和Columns()来做,所以这行应该是: pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial 。 另外,你不能做mySheet.Range([formula]) 。 这需要打破。 你会反而想做 Set addSection = Worksheet ...
-
VBA公式无法正常工作(VBA formula does not work properly)[2023-09-12]
尝试这个。 您需要使用工作簿/工作表来限定所有范围 With ActiveWorkbook.Sheets(1) ISINcheck = WorksheetFunction.CountIf(.Range(.Cells(2, intBB2), .Cells(LastRow, intBB2)), _ .Range(.Cells(2, intBB2))) _ = WorksheetFunction.CountA(.Range(.Cells(2, int ... -
Excel公式或VBA(Excel Formula or VBA)[2022-03-24]
你可以使用模函数[1] MOD(number, divisor) 相应地标记特定周。 也就是说,结合if ,你可以检查mod(weekNumber,interval)是否等于零(或者你的情况下是一个),然后显示所需的内容。 [1] http://office.microsoft.com/en-001/excel-help/mod-function-HP010342698.aspx You can use the modulo-function [1] MOD(number, divisor) to fl ... -
数组公式到VBA(Array formula to VBA)[2022-06-24]
第一个提示: 在你的VBA公式的末尾,你有"" : ...Data!$K2:$AT108264)))),"")" 在VBA中,如果要在公式中包含引号,则应使用double qoutes: """"而不是"" 。 第二个提示: 无需使用循环将公式应用于范围中的每个单元格: For r = 4 To 214 Sheet2.Cells(r, 210).FormulaArray = "=IFERROR(...C4,...)" Next r 如果你愿意, 你的代码会快得多 (第2A列是HB ): She ... -
将2个表合并为1.将值彼此相邻,即vba或公式(Merge 2 tables into 1. Add values next to each other, vba or formula)[2023-12-09]
快速和简单的答案,我过滤,以获得独特的组合,只有粘贴作为价值观,然后单位我对产品sumif对一张桌子得到单位,然后在它旁边的单元(销售)我做了一个sumif反对产品组合上的第二张表。 虽然在VBA中完成 然后运行一些代码,检查两个单位和销售额是否均等于0(每个单元格旁边都是0),然后移除整个行。 如果有人想看代码。 让我知道,我会发布。 第一步是创建一个包含所有组合的用户界面,并仅复制/粘贴唯一值.... Columns("A:A").Select Application.CutCopyMode = Fal ... -
你可能不得不玩这个但我认为这应该有效。 我们的想法是使公式字符串动态化,以便随着行的变化而变化,对吗? 另一种可能的方法是在串联到字符串后保存预期的公式并以这种方式插入。 With Cells(x + 1, 3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= _ "=OFFSET(CategoryStart,MATCH ...
-
SUPPRODUCT公式在VBA中(SUMPRODUCT Formula in VBA)[2023-10-30]
两种可能的简单解决方案,考虑到工作表函数方法不适用于您使用的数组大小: 首先,添加公式,然后将其替换为其值 With activesheet.Range("F12") .Formula =" =SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)" .Value2 = .Value2 End With 其次,使用Evaluate : With Activesheet .range( ... -
如何在VBA中添加圆值?(How to add round values in VBA?)[2022-05-24]
自从你 想要将各个圆值一起添加 那么你想要这个公式 Range("J6:J" & lastRow).Formula = "=ROUND(H6,0)+ROUND(I6,0)" since you want to add individually round values together then you want this formula Range("J6:J" & lastRow).Formula = "=ROUND(H6,0)+ROUND(I6,0)" -
你可以这样做: For i = 4 to LastRow3 Cells(i, 5).Value = Cells(i, 1).Value & " " & Cells(i, 2).Value Next 这是简单的循环,循环遍历第五列(E),列4和LastRow3之间的行,并将该单元格的值设置为在同一行中连接的A列和B列。 You could do this alternatively: For i = 4 to LastRow3 Cells(i, 5).Value = Cells(i, 1). ...
-
此代码适用于您的示例 - 但YMMV将如何扩展到现实生活中更复杂的公式。 但是,它会让你开始 - 然后一些 - 如何递归Range的Precedents集合。 我已经对代码进行了评论,但最简单的理解方法是使用F8在调试模式中逐步完成。 Option Explicit Sub Test() Dim ws As Worksheet Dim rng As Range Dim strOutput As String 'your test case from the origina ...