Find "****-***" in a text string

[Grimm127]

Hi All you Guru's,
I have a set of document numbers that are mixed in a with other wording in a cell.
I just want to pull from the text string the 7 digit combination with the dash in the middle.
Something that looks like this = 123-4567 or 552-7743. The doc number is set like that
However each text box will have a different set of numbers.
I was thinking of a find ***-**** to pull what I am looking for.

Any suggestions?

Thank you

 

[Grimm127]

What is your version of Excel actually? It would matter quite a bit.

Version 2022 (Build 14931.20724)

 

[JvdV]

Using some sample data the other contributers posted, maybe try the following:

Book1
	A	B
2	aaaaaaa 123-4567 mmmmmmmmmm	123-4567
3	111111111 552-7743 222222222222	552-7743
4	Something that looks like this = 123-4567	123-4567
5	or 552-7743. The doc number is set like that	552-7743
6	or aaa-aaaa 552-7743. The doc number is set like that	552-7743
7	or bbb-bbbb 552-7743. The doc number is set like that	552-7743
8	HELP DESK OVERAGES PROCUREMENT-NOV22-166-2022	166-2022
9	OwnBackup:Enhcd Sandbox Seed(Mar-22-Jan23)300-2022	300-2022
10	test 123-12	Nothing Found
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=MAP(A2:A10,LAMBDA(x,LET(y,MID(x,SEQUENCE(LEN(x)),8),@FILTER(y,(MID(y,4,1)="-")*(ISNUMBER(--SUBSTITUTE(y&9999,"-",,1)))*(LEN(y)=8),"Nothing Found"))))
Dynamic array formulas.

 

[JEC]

Another option

Book1
	A	B
1	aaaaaaa 123-4567 mmmmmmmmmm	123-4567
2	111111111 552-7743 222222222222	552-7743
3	Something that looks like this = 123-4567	123-4567
4	or 552-7743. The doc number is set like that	552-7743
5	or aaa-aaaa 552-7743. The doc number is set like that	552-7743
6	or bbb-bbbb 552-7743. The doc number is set like that	552-7743
7	HELP DESK OVERAGES PROCUREMENT-NOV22-166-2022	166-2022
8	OwnBackup:Enhcd Sandbox Seed(Mar-22-Jan23)300-2022	300-2022
9	test 123-12	Nothing found
Sheet1

Cell Formulas
Range	Formula
B1:B9	B1	=LET(x,IFERROR(--MID(A1,UNIQUE(SEARCH("-",A1,SEQUENCE(LEN(A1))))-{3,-1},{3,4}),0),TEXTJOIN("-",,FILTER(x,MMULT((x>0)*(LEN(INDEX(x,,2))=4),{1;1}),"Nothing found")))

 

[Joe4]

Version 2022 (Build 14931.20724)

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

Another couple of worksheet formula options if the functions are available.

23 02 14.xlsm
	A	B	C
1	aaaaaaa 123-4567 mmmmmmmmmm	123-4567	123-4567
2	111111111 552-7743 222222222222	552-7743	552-7743
3	Something that looks like this = 123-4567	123-4567	123-4567
4	or 552-7743. The doc number is set like that	552-7743	552-7743
5	or aaa-aaaa 552-7743. The doc number is set like that	552-7743	552-7743
6	or bbb-bbbb 552-7743. The doc number is set like that	552-7743	552-7743
7	HELP DESK OVERAGES PROCUREMENT-NOV22-166-2022	166-2022	166-2022
8	OwnBackup:Enhcd Sandbox Seed(Mar-22-Jan23)300-2022	300-2022	300-2022
9	test 123-12
Doc Num

Cell Formulas
Range	Formula
C1:C9	C1	=BYROW(A1:A9,LAMBDA(r,LET(t,"111-1111",c,MID(r,SEQUENCE(LEN(r)),1),MID(r,FIND(t,CONCAT(IF(ISNUMBER(c+0),1,c)," ",t)),8))))
B1:B9	B1	=LET(t,"111-1111",c,MID(A1,SEQUENCE(LEN(A1)),1),MID(A1,FIND(t,CONCAT(IF(ISNUMBER(c+0),1,c)," ",t)),8))
Dynamic array formulas.