Formula To Find Only Instances of 4 digits In A ROW Within Cell And Return It To The Adjacent Cell

[CONFUSED_AS_USUAL]

Example:

SOURCE	RETURN
#5364	5364
#5571	5571
1 P/O#5159/1 WEB SALE	5159
20 SOLD P/O#5221	5221
24 SOLD P/O#4968	4968
24 SOLD P/O#5149	5149
3 SOLD P/O#5193	5193
36 SOLD P/O#5322	5322
6 SOLD P/O#5245	5245
lee tz qc
P/O#4379	4379
P/O#4409	4409
P/O#4497	4497
P/O#4497	4497

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks you

 

[jtakw]

Hi,

If the numbers you're looking for are Always preceded by the symbol # and Always 4 digits:

00 HTML Conversions.xlsm
	A	B
1	SOURCE	RETURN
2	#5364	5364
3	#5571	5571
4	1 P/O#5159/1 WEB SALE	5159
5	20 SOLD P/O#5221	5221
6	24 SOLD P/O#4968	4968
7	24 SOLD P/O#5149	5149
8	3 SOLD P/O#5193	5193
9	36 SOLD P/O#5322	5322
10	6 SOLD P/O#5245	5245
11	lee tz qc
12	P/O#4379	4379
13	P/O#4409	4409
14	P/O#4497	4497
15	P/O#4497	4497
Sheet9

Cell Formulas
Range	Formula
B2:B15	B2	=IFERROR(MID(A2,FIND("#",A2)+1,4)+0,"")

Formula copied down.

 

[CONFUSED_AS_USUAL]

Close - but no... there will be cases where the 4 numbers may not be preceded by a symbol. It could be anything or nothing.

 

[Peter_SSs]

Apart from the issue raised about the preceding "#", it is also not clear whether your data could possibly contain a string of more than 4 digits and, if so, what should happen in that case.
Anyway, one way to accomplish this would be to use a user-defined function. I've included two such functions below for you to consider. I have assumed that you want exactly 4 digits rather than 4 or more. I have made the UDFs flexible by allowing an optional 2nd argument if you wanted to search for a different number of digits - for example, as shown in the last row of my sample worksheet.

To implement a UDF..
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 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 ExtrDigits(s As String, Optional lNumDigits As Long = 4) As Variant
  Static RX As Object
 
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^|\D)(\d{" & lNumDigits & "})(\D|$)"
  ExtrDigits = ""
  If RX.Test(s) Then ExtrDigits = CLng(RX.Execute(s)(0).submatches(1))
End Function


Function GetDigits(s As String, Optional lNumDigits As Long = 4) As Variant
  Dim i As Long
  Dim sLike As String
 
  GetDigits = ""
  s = "x" & s & "x"
  sLike = "[!0-9]" & String(lNumDigits, "#") & "[!0-9]"
  For i = 1 To Len(s) - lNumDigits - 1
    If Mid(s, i, lNumDigits + 2) Like sLike Then
      GetDigits = CLng(Mid(s, i + 1, lNumDigits))
      Exit For
    End If
  Next i
End Function

00 HTML Conversions.xlsm
	A	B	C	D
1	SOURCE
2	#5364	5364		5364
3	#5571	5571		5571
4	1 P/O#5159/1 WEB SALE	5159		5159
5	20 SOLD P/O#5221	5221		5221
6	24 SOLD P/O#4968	4968		4968
7	24 SOLD P/O#5149	5149		5149
8	3 SOLD P/O#5193	5193		5193
9	36 SOLD P/O#5322	5322		5322
10	6 SOLD P/O#5245	5245		5245
11	lee tz qc
12	P/O#4379	4379		4379
13	P/O#4409	4409		4409
14	P/O#4497	4497		4497
15	P/O#4497	4497		4497
16	5632X	5632		5632
17	W4576	4576		4576
18
19	P/O#43798	43798		43798
Sheet3

Cell Formulas
Range	Formula
B2:B17	B2	=ExtrDigits(A2)
D2:D17	D2	=GetDigits(A2)
B19	B19	=ExtrDigits(A19,5)
D19	D19	=GetDigits(A19,5)

 

[CONFUSED_AS_USUAL]

It appears to work like a charm. Many thanks Peter.

 

[Peter_SSs]

It appears to work like a charm. Many thanks Peter.

You are welcome. Thanks for letting us know.

 

[spamspambaconspam]

Apart from the issue raised about the preceding "#", it is also not clear whether your data could possibly contain a string of more than 4 digits and, if so, what should happen in that case.
Anyway, one way to accomplish this would be to use a user-defined function. I've included two such functions below for you to consider. I have assumed that you want exactly 4 digits rather than 4 or more. I have made the UDFs flexible by allowing an optional 2nd argument if you wanted to search for a different number of digits - for example, as shown in the last row of my sample worksheet.

To implement a UDF..
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 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)

Gawwwwwd, I love you because
a) you wrote code that does EXACTLY what the OP/I need, and
b) you included help on how to add a UDF to a file!!

Thank you for your awesome response, from someone who found it 2+ years later

 

[Peter_SSs]

Gawwwwwd, I love you because
a) you wrote code that does EXACTLY what the OP/I need, and
b) you included help on how to add a UDF to a file!!

Thank you for your awesome response, from someone who found it 2+ years later

You're welcome.