Extract 7 digit number from text

[Jackadair]

Evening!

First post, mostly be back in the future if this gets answered!

Need to extract 7 digit number from text

Example

Abbb bbb b 1234567 (os3)
Is so3 bb | 7654321 (33pp)
So3 bea | 1111111(or8)

Need a formula that would extract the 7 digit number from all those text at once so it would look like

1234567
7654321
1111111

 

[lrobbo314]

How about this?

COMPSTAT Template1 (version 2).xlsb
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
Sheet3

Sub EXTRACTSEVEN()
Dim r As Range:         Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("VBScript.RegExp")
    .Pattern = "\d{7}"
    For i = 1 To UBound(AR)
        AR(i, 1) = .Execute(AR(i, 1))(0)
    Next i
End With

r.Offset(, 1) = AR
End Sub

 

[Rick Rothstein]

If the numbers you want will always be 7 digits long and every cell will have a 7-digit number in the text and there will never be a longer number in any of the cells, then put the first number (1234567) in cell B1, the second number (7654321) in cell B2, then select cell B3 (its empty) and press CTRL+E and Excel should pull all the numbers for you.

 

[cmowla]

Here's an Excel formula option, where you would change the two 7's to a different number for a number with n digits. (But the formula displayed works for 7 digit numbers. And if you change it to 6 to look for 6 digit numbers instead, and there is a 7 digit number, it will show the first 6 digits of the 7 digit number, etc.):

b.xlsb
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=MID(A1,SEARCH(REPT("1",7),TEXTJOIN(,,IFERROR(MID(A1,SEQUENCE(,LEN(A1)),1)*0+1,2))),7)

 

[bebo021999]

Or (works from Ex 2010)

Test.xlsm
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:" & LEN(A1))),7)+0,1)

 

[cmowla]

Here's an alternate/shorter solution for 365:

b.xlsb
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=INDEX(SORT(MID(SUBSTITUTE(A1," ",""),SEQUENCE(,LEN(A1)),7)+0,,,1),1)

 

[Peter_SSs]

Without knowing what might really be in the data, I would say that posts 5 & 6 suggestions are not robust. Examples of incorrect results:

22 09 23.xlsm
	A	B
1	A12561E2 Abbb 1234567 (os3)	1256100
2	A12DEC22 Abbb 1234567 (os3)	44907
Extract (2)

Cell Formulas
Range	Formula
B1	B1	=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:" & LEN(A1))),7)+0,1)
B2	B2	=INDEX(SORT(MID(SUBSTITUTE(A2," ",""),SEQUENCE(,LEN(A2)),7)+0,,,1),1)

Assuming numbers longer than 7 digits are not possible, I would suggest some minor tweaks to the post 4 formula.
The double quotes in REPT can be omitted
The addition near the end can be avoided by using zero instead of 1
The two blank arguments in TEXTJOIN can be avoided by using CONCAT instead

22 09 23.xlsm
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
4	A12561E2 Abbb 1234567 (os3)	1234567
5	A12DEC22 Abbb 1234567 (os3)	1234567
Extract

Cell Formulas
Range	Formula
B1:B5	B1	=MID(A1,SEARCH(REPT(0,7),CONCAT(IFERROR(MID(A1,SEQUENCE(LEN(A1)),1)*0,2))),7)

 

[Jackadair]

How about this?

COMPSTAT Template1 (version 2).xlsb
	A	B
1	Abbb bbb b 1234567 (os3)	1234567
2	Is so3 bb | 7654321 (33pp)	7654321
3	So3 bea | 1111111(or8)	1111111
Sheet3

Sub EXTRACTSEVEN()
Dim r As Range:         Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("VBScript.RegExp")
    .Pattern = "\d{7}"
    For i = 1 To UBound(AR)
        AR(i, 1) = .Execute(AR(i, 1))(0)
    Next i
End With

r.Offset(, 1) = AR
End Sub

Brilliant, apologise I am fairly new to excel. Some functions on excel aren't avaliable due to this being a work computer.


I don't belive I'm able to add a VBA

 

[Peter_SSs]

Brilliant ...

I don't belive I'm able to add a VBA

But you were able to implement the post #2 suggestion?

 

[cmowla]

Assuming numbers longer than 7 digits are not possible, I would suggest some minor tweaks to the post 4 formula.

I don't see the difference between the output of my formula in post#4 and yours in this regard. For example, I put an 8 digit number in the cell, and both formulas outputted the first 7 digits of it.

(And my formula gave the same outputs as the ones you tested yours with.)

But thanks for making improvements to it!

(And thanks for catching that my second formula was faulty.)