从VBA中的字符串中提取数字 [英] Extract number from string in VBA

问题描述

QUANTITY SUPPLY< = DAYS SUPPLY | 30 23天

我通过两个函数发送这些字符串，只需选出两个数字，并将它们解析为相应的单元格，然后再分配剩余的数据。选择天数（23）的功能正常工作，但选择30的功能不是。我一直在测试它，它似乎正在解析出30以及整个字符串之前，当我想要的是30。在上述字符串的情况下，它返回QUANTITY SUPPLY< = DAYS SUPPLY | 30当我想要它返回是30.我已经看了功能，找不到问题。对于这个问题的任何帮助将不胜感激！

 公共函数extractQLlMax（cellRow，cellColumn）As String 
 qlm =单元格（cellRow，cellColumn）.Value 
 extractQLlMax = qlm 
如果extractQLinfoBool =是而不是InStr（1，qlm，IN）= 0然后
如果InStr（1，qlm ，QUANTITY SUPPLY）> 0然后
 pipeIndex = InStr（1，qlm，|）
 inIndex = InStr（1，qlm，IN）
 extractQLlMax = Mid（qlm，pipeIndex，inIndex  -  pipeIndex） 
 End If 
 inIndex = InStr（1，qlm，IN）
 extractQLlMax = Mid（qlm，1，inIndex  -  2）
 ElseIf extractQLinfoBool =Yes InStr（1，qlm，FILL）= 0然后
 perIndex = InStr（1，qlm，PER）
 extractQLlMax = Mid（qlm，1，perIndex  -  2）
结束如果
结束函数
  

解决方案

VBA中的拆分功能？如果总是以管道分隔，您可以尝试：

 公共函数extractQLlMax（cellRow，cellColumn）As String 
 Dim X as Variant 
 qlm = Cells（cellRow，cellColumn）.Value 
 extractQLlMax = qlm 
 
如果extractQLinfoBool =是而不是InStr（1，qlm，IN） = 0然后
如果InStr（1，qlm，QUANTITY SUPPLY）> 0然后
x = Split（qlm，|）
 extractQLlMax = X（ubound（x））
 ElseIf extractQLinfoBool =是而不是InStr（1，qlm，FILL） = 0然后
 perIndex = InStr（1，qlm，PER）
 extractQLlMax = Mid（qlm，1，perIndex  -  2）
 End If 
 End Function 
  

I have cells in vba that contain strings like this:

QUANTITY SUPPLY <= DAYS SUPPLY|30 IN 23 DAYS

I send these strings through two functions that just picks out the two numbers and parses them into the appropriate cells and just scraps the rest. The function that picks out the days number (23) is working fine but the function that picks out the 30 is not. I have been testing it and it seems to be parsing out the 30 as well as the whole string before it when all I want is the 30. In the case of the above string, it is returning "QUANTITY SUPPLY <= DAYS SUPPLY|30" when all I want it to return is the 30. I have looked at the function and cannot find the issue. Any help with this issue would be greatly appreciated!

Public Function extractQLlMax(cellRow, cellColumn) As String
    qlm = Cells(cellRow, cellColumn).Value
    extractQLlMax = qlm
    If extractQLinfoBool = "Yes" And Not InStr(1, qlm, "IN") = 0 Then
        If InStr(1, qlm, "QUANTITY SUPPLY") > 0 Then
        pipeIndex = InStr(1, qlm, "|")
        inIndex = InStr(1, qlm, "IN")
        extractQLlMax = Mid(qlm, pipeIndex, inIndex  - pipeIndex)
        End If
        inIndex = InStr(1, qlm, "IN")
        extractQLlMax = Mid(qlm, 1, inIndex - 2)
    ElseIf extractQLinfoBool = "Yes" And Not InStr(1, qlm, "FILL") = 0 Then
        perIndex = InStr(1, qlm, "PER")
        extractQLlMax = Mid(qlm, 1, perIndex - 2)
    End If
End Function

解决方案

Have you considered using the "Split" function in VBA? If it is always pipe delimited, you could try:

Public Function extractQLlMax(cellRow, cellColumn) As String
    Dim X as Variant
    qlm = Cells(cellRow, cellColumn).Value
    extractQLlMax = qlm

    If extractQLinfoBool = "Yes" And Not InStr(1, qlm, "IN") = 0 Then
        If InStr(1, qlm, "QUANTITY SUPPLY") > 0 Then
        x = Split(qlm,"|")
        extractQLlMax = X(ubound(x))
    ElseIf extractQLinfoBool = "Yes" And Not InStr(1, qlm, "FILL") = 0 Then
        perIndex = InStr(1, qlm, "PER")
        extractQLlMax = Mid(qlm, 1, perIndex - 2)
    End If
End Function

这篇关于从VBA中的字符串中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！