Extracting 6 digit numbers

[luke1515]

Here is a fun one.....

I need to extract "only" 6 Digit (consecuitve) number(s) from a cell. In the cell, there can be any combination of numbers (dates, prices, P/N's) and text.

I need a formula that will only extract 6 digit numbers.

EXAMPLE of a cell - Pete wire price list 2013 992813-44 Order 99496 placed Janurary 2011 for qty 200 at price of $98.63.

So the only thing I would want extracted is the 6 digit consecutive number in RED. notice the order number after is 5 digits which I would want ignored.

I tried this formula: =TEXT(LOOKUP(10^6,MID(SUBSTITUTE(B197," ","x"),ROW(INDIRECT("1:"&LEN(B197)-7)),6)+0),"000000") but it isn't consistent.

The numbers aren't in a consistent order - could start with 1-9 and is fine if it extracts the "-44"

Thanks for all your help

 

[XOR LX]

Well, that's going to be extremely complex unless you give us something a bit more to go on.

For example, although there may be other numbers in the string, can you at least guarantee that the desired extraction is the only such number having six digits?

If not, and there my be more than one such number, is there anything else that can help identify which of the six-digit numbers in the string is to be extracted?

Regards

 

[luke1515]

Completely understand. But no I can't. So if there is 999999-99 999999 and 9999-999999. I would want all three or at least one of the 999999's. Majority of the time it will be entered in as 999999 (simply six digits) or 999999-99 with a dash and a two digit number that follows, if that helps at all.

I told you it would be a fun one.

 

[NeonRedSharpie]

Function extractNum(target As Range, length As Integer) As Variant

    Dim value As String
    value = target.value
        Debug.Print Mid(value, 1, length)
        Debug.Print IsNumeric(Mid(value, 1, length))


    For x = 2 To Len(value) + length


        For y = 0 To length - 1
            Select Case IsNumeric(Mid(value, y + x, 1))
                Case True
                Case False
                GoTo notNumber
            End Select


            Select Case Mid(value, y + x, y)
                Case " "
                    GoTo notNumber
                Case Else
            End Select
        Next y
        If IsNumeric(Mid(value, x - 1, 1)) = False And IsNumeric(Mid(value, length + 1, 1)) = False Then
    
            extractNum = Mid(value, x, length)
            Exit Function


        End If
notNumber:
    Next x


End Function

Here's a custom function that will allow you to get the FIRST instance of any length number. The inputs are the cell you want, and then the length. So if you want that 5 length number, it can pull that too.



------edit-----
it works for 6 numbers...tweaking for 5. Apparently that logic isn't there yet.

 

[XOR LX]

Try this array formula**, based on a string in A2:

=IFERROR(MID($A2,SMALL(IF(MMULT(ABS(ISNUMBER(0+MID(MID(" "&$A2,ROW(INDIRECT("1:"&LEN(" "&$A2)-7)),8),{1,2,3,4,5,6,7,8},1))-{1,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1})=8,ROW(INDIRECT("1:"&LEN($A2)-7))),COLUMNS($A:A)),6),"")

Copy to the right to get the 2nd, 3rd, etc. 6-digit number in the string. Also copy down to give similar results for strings in A3, A4, etc.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[luke1515]

Simply amazing. I will tell tales of your kindness and wisdom throughtout my travels XOR LX

 

[XOR LX]

Simply amazing. I will tell tales of your kindness and wisdom throughtout my travels XOR LX

Hahaha!! Don't think I've ever have a compliment such as that! Cheers!

Fame awaits!

 

[wlovell88]

Try this array formula**, based on a string in A2:

=IFERROR(MID($A2,SMALL(IF(MMULT(ABS(ISNUMBER(0+MID(MID(" "&$A2,ROW(INDIRECT("1:"&LEN(" "&$A2)-7)),8),{1,2,3,4,5,6,7,8},1))-{1,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1})=8,ROW(INDIRECT("1:"&LEN($A2)-7))),COLUMNS($A:A)),6),"")

Copy to the right to get the 2nd, 3rd, etc. 6-digit number in the string. Also copy down to give similar results for strings in A3, A4, etc.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

can you share what the formula would be to work for 8 digit numbers? i can't seem to figure it out. this would be a huge lifesaver!

 

[muldercs]

This formula misses the last 6 digit number if you have more than one and copy accross the rows the correction would be to remove the two -7 in the formula to this

=IFERROR(MID($A2,SMALL(IF(MMULT(ABS(ISNUMBER(0+MID(MID(" "&$A2,ROW(INDIRECT("1:"&LEN(" "&$A2))),8),{1,2,3,4,5,6,7,8},1))-{1,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1})=8,ROW(INDIRECT("1:"&LEN($A2)))),COLUMNS($A:A)),6),"")

 

[lrobbo314]

I thought this would be a fun one to mess with. Might be overkill, but I think it's cool. It will return multiple matches if they exist and you can easily change the length of the string you want to return.

L1515
	A
1	Pete wire price list 2013 992813-44 Order 99496 placed Janurary 2011 for qty 200 at price of $98.63
2
3	992813
Sheet1

Cell Formulas
Range	Formula
A3	A3	=LAMBDA(text,t_len,LET(SL, LAMBDA(t,MID(t,SEQUENCE(LEN(t)),1)),RDC, LAMBDA(text,chr,LET(a,SL(text),s,SCAN(1,a,LAMBDA(s,c,IF(c=chr,s+1,0))),TEXTJOIN("",,MAP(a,s,LAMBDA(x,y,IF(y>1,"",x)))))),u,UNICHAR(955),t,TEXTSPLIT(RDC(TEXTJOIN("",,MAP(CODE(SL(text)),LAMBDA(x,IF(AND(x>47,x<58),CHAR(x),u)))),u),,u),FILTER(t,LEN(t)=t_len)+0))(A1,6)

=LAMBDA(text,t_len,
    LET(
        SL, LAMBDA(t,MID(t,SEQUENCE(LEN(t)),1)),
        RDC, LAMBDA(text,chr,
            LET(
                a,SL(text),
                s,SCAN(1,a,LAMBDA(s,c,IF(c=chr,s+1,0))),
                TEXTJOIN("",,MAP(a,s,LAMBDA(x,y,IF(y>1,"",x))))
                )
            ),
        u,UNICHAR(955),
        t,TEXTSPLIT(RDC(TEXTJOIN("",,MAP(CODE(SL(text)),LAMBDA(x,IF(AND(x>47,x<58),CHAR(x),u)))),u),,u),
        FILTER(t,LEN(t)=t_len)+0
    )
)(A1,6)

Also, here is a way using VBA and Regular Expressions.

Function EXTNUM(s As String, n As Integer)
With CreateObject("VBScript.RegExp")
    .Pattern = "\d{" & n & "}"
    EXTNUM = Val(.Execute(s)(0))
End With
End Function