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

 

[Sufiyan97]

Try

Book10
	A	B	C
1
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
7
8
9
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,SEARCH("???-????",A2),8)

 

[Grimm127]

Try

Book10
	A	B	C
1
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
7
8
9
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,SEARCH("???-????",A2),8)

SOOOO very close! The problem is there is another set of values with alpha characters with a dash as well. It has to search for digits only.

 

[Sufiyan97]

You mean numbers only?

 

[Grimm127]

You mean numbers only?

Yes, it has to be numbers only withing the XXX-XXXX format

 

[Grimm127]

SOOOO very close! The problem is there is another set of values with alpha characters with a dash as well. It has to search for digits only.

For instance with your formula it will look at APR-2023 or Mar-June and return those values. I require only in that format in a numerical combination 123-4562, etc

 

[Sufiyan97]

There will be defenitely shorter ways
but you can Try this for now

Race Query.xlsx
	A	B
1
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
9
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=MID(IF(ISNUMBER(LEFT(MID(A2,SEARCH("???-????",A2),8))+0),MID(A2,SEARCH("???-????",A2),8),SUBSTITUTE(A2,MID(A2,SEARCH("???-????",A2),8),"")),SEARCH("???-????",IF(ISNUMBER(LEFT(MID(A2,SEARCH("???-????",A2),8))+0),MID(A2,SEARCH("???-????",A2),8),SUBSTITUTE(A2,MID(A2,SEARCH("???-????",A2),8),""))),8)

 

[Peter_SSs]

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’)

One option would be to employ a user-defined function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DocNum(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "\d{3}\-\d{4}"
    If .Test(s) Then DocNum = .Execute(s)(0)
  End With
End Function

Grimm127.xlsm
	A	B
1
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
8	or aaa-aaaa no doc number
9	or bbb-bbbb 552-7743. The doc number is set like that	552-7743
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=DocNum(A2)

 

[Grimm127]

There will be defenitely shorter ways
but

Here are three samples:

HELP DESK OVERAGES PROCUREMENT-NOV22-166-2022

OwnBackup:Enhcd Sandbox Seed(Mar-22-Jan23)300-2022

Ownbackup:Enhanced Sandbox (Jun22-Feb23) 300-2022

 

[JvdV]

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