从字符串中提取第n个发生数 [英] Extract nth Occurance Number from String

问题描述

我有一列包含文本字符串的单元格，其中包含文本和数字。我发现一个很好的公式，找到字符串中的第一个数字，并将其提取到相应的单元格，例如

文本字符串：初始佣金9,999.99，然后更新佣金从99日起$ 9.9

输入此公式 = LOOKUP（99 ^ 99， - （0& MID ，MIN（SEARCH（{0,1,2,3,4,5,6,7,8,9}，F8&安培; 0123456789）），ROW（$ 1：$ 10000））））相邻的单元格将提取第一个数字9,999.99。

这不是我的公式，而是我发现和爱，但希望能够表明我也想提取第二个出现的数字，也是第3个出现的数字。基本上所有的字符串中的数字，分开的单元格。

你们中有谁聪明的人能够建议一个修改公式来允许这个？ b
$ b

提前感谢

解决方案

所以这是提出的功能 - 它使用一个简单的2状态方法一般解决方案的问题是使用逗号作为数千分隔符和部分文本在问题中。所以999,999可以是一个数字或两个单独的数字。此外，99,99,99虽然不是有效的数字从ISNUMERIC返回TRUE，从VAL返回99。你可以尝试找到最长可能的数字，但是如果面临诸如99,999,99这样的字符串，你将不得不回溯，一个简单的2状态模型将无法正常工作。

<所以我认为在这种简单的级别中唯一实际的解决方案是使用一个参数来调用这个函数，该参数告诉它（a）将逗号作为一个千位分隔符（基本上忽略它）或（b）将其视为分隔符，因此每当遇到逗号时停止向数字中添加字符。

应该很容易就能处理初始减号如果需要，但是现在还没有。

 函数GetNthNumber（s As String，n As Integer，Optional CommaAsThousands As Boolean = True）As Variant 
 
 Dim c，testNumber，number As String 
 
'忽略逗号如果被视为千位分隔符
如果CommaAsThousands Then s = Replace （s，，，）
 
s = s& x
 
 Dim i，j，count As Integer 
 
 Dim inNumber As Boolean 
 
 inNumber = False 
 
 '通过字符串的每个字符
 
对于i = 1到Len（s）
 
c = Mid（s，i，1）
 
'一个数字的一​​部分 - 附加新的字符或完成号码
 
如果inNumber然后
如果IsNumeric（number& c）And（CommaAsThousands或c  - ，）然后
 number = number& c 
 Else 
 inNumber = False 
如果count = n然后退出对于
结束如果
 Else 
 
'不是数字的一部分 - 开始新的号码或不做任何事情
 
如果IsNumeric（c）然后
 inNumber = True 
 number = c 
 count = count + 1 
 End If 
 
 End If 
 Next i 
 
'返回第n个数字或#Value错误
 
如果count = n然后
 GetNthNumber = Val（number）
 Else 
 GetNthNumber = CVErr（xlErrValue）
如果
结束函数
  

以两种不同的方式调用结果（第一行以逗号分隔数为千位，第二行为逗号作为分隔符）： -

I have a column containing cells of text string which includes text and numbers. I found a great formula that finds the first number in the string and extracts it to the corresponding cell e.g.

Text String: "Initial commission of £9,999.99, then renewal commission of £9.9 from month 99"

Entering this formula =LOOKUP(99^99,--("0"&MID(F8,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F8&"0123456789")),ROW($1:$10000)))) adjacent to the cell will extract the first number of 9,999.99.

This is not my formula but one that I found and love but would like to be able to indicate that I would also like to extract the 2nd occurring number and also the 3rd occurring number. Basically all the numbers in the string, into separate cells.

Are any of you clever people able to suggest an edit on the formula to allow this?

Thanks in advance.

解决方案

So this is the proposed function - it uses a simple 2-state approach. The problem with a general solution is with using a comma both as a thousands separator and part of the text as in the question. So 999,999 could be a single number or two separate numbers. Also, 99,99,99 although not a valid number returns TRUE from ISNUMERIC and 99 from VAL. You could try and find the longest possible number but if faced with a string such as 99,999,99 you would have to backtrack and a simple 2-state model wouldn't work.

So I think the only practical solution at this kind of simple level is for the function to be called with a parameter which tells it either (a) to treat a comma as a thousands separator (and basically ignore it) or (b) to treat it as a delimiter and therefore to stop adding characters to a number whenever a comma is encountered.

It should be easy enough to be able to process an initial minus sign if required, but that isn't in the present version yet.

Function GetNthNumber(s As String, n As Integer, Optional CommaAsThousands As Boolean = True) As Variant

Dim c, testNumber, number As String

' Ignore commas if treated as thousands separator
If CommaAsThousands Then s = Replace(s, ",", "")

s = s & "x"

Dim i, j, count As Integer

Dim inNumber As Boolean

inNumber = False

' Loop through each character of string

For i = 1 To Len(s)

c = Mid(s, i, 1)

' Part of a number - append new character or finish number

    If inNumber Then
        If IsNumeric(number & c) And (CommaAsThousands Or c <> ",") Then
            number = number & c
        Else
            inNumber = False
            If count = n Then Exit For
        End If
    Else

' Not part of a number - start new number or do nothing

        If IsNumeric(c) Then
            inNumber = True
            number = c
            count = count + 1
        End If

    End If
Next i

'Return nth number or #Value error

If count = n Then
    GetNthNumber = Val(number)
Else
    GetNthNumber = CVErr(xlErrValue)
End If
End Function

Here are the results when called in the two different ways (first row with comma as thousands separator and second row with comma as delimiter):-

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