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

 

[Peter_SSs]

I don't see the difference between the output of my formula in post#4 and yours in this regard.

I wasn't suggesting there was a difference. I was attempting to re-emphasising the point (that you had already made) to the OP in case it was an issue for them.

 

[Jackadair]

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)

Looks brilliant.

I've try adding this formula into excel, litteraly copy and paste into A2 cell ( all my data is in A1)

Not really getting a result, any assistance?

Really appreciate your help!

 

[cmowla]

@Peter_SSs
My second formula passes all of your tests with the following adjustment (2 characters longer than your adjustment to post #4's formula):

b.xlsb
	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
Sheet2 (2)

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

 

[Peter_SSs]

all my data is in A1

Do you mean like this?

22 09 23.xlsm
	A
1	Abbb bbb b 1234567 (os3)(n) Is so3 bb | 7654321 (33pp) So3 bea | 1111111(or8)
Extract (3)

My second formula passes all of your tests with the following adjustment ..

I assume that you are addressing me? What about this?

22 09 23.xlsm
	A	B
1	A1 2 2 3 1 1 2 2 Abbb 1234567 (os3)	1223112
Extract (2)

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

 

[cmowla]

I assume that you are addressing me? What about this?

22 09 23.xlsm
	A	B
1	A1 2 2 3 1 1 2 2 Abbb 1234567 (os3)	1223112
Extract (2)

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

Ah, you're good. Here's the obvious fix (no change in length):

b.xlsb
	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
6	A1 2 2 3 1 1 2 2 Abbb 1234567 (os3)	1234567
Sheet2 (2)

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

 

[Peter_SSs]

Here's the obvious fix (no change in length):

In my experience, any attempt to deal with such a 'number string' other than character-by-character is fraught with risk. Why not stick to the earlier one that works?

What about?

22 09 23.xlsm
	A	B
1	A1111.11 Abbb 1234567 (os3)	1111
Extract (2)

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