Excel formula to extract the number in a text

[LAY2022]

HI Masters,

I need help in getting the invoice number in a cell with a text string.

Book1
	A	B	C
1	SUPPLIER	DETAILS	INVOICE#
2	ABC CO	INV#: 66042262 - 522.00 INV#: 66042335 - 960.00 Tools	66042262 66402335
3	ABC CO	INV#: 66038272 Pvc	66038272
4	ABC CO	INV#: 66028693	66028693
Sheet1

Thanks,
Lay

 

[lrobbo314]

One way.

EXCEL
	A	B	C	D
1	SUPPLIER	DETAILS	INVOICE#	Custom Function
2	ABC CO	INV#: 66042262 - 522.00 INV#: 66042335 - 960.00 Tools	66042262 66402335	66042262 66042335
3	ABC CO	INV#: 66038272 Pvc	66038272	66038272
4	ABC CO	INV#: 66028693	66028693	66028693
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=rx(B2)

Function RX(s As String)
Dim SD As Object:   Set SD = CreateObject("Scripting.Dictionary")
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "INV#: (\d+)"
    Set matches = .Execute(s)
    For Each m In matches
        SD.Add m.submatches(0), Null
    Next m
End With

For Each itm In SD
    RX = RX & itm & Chr(10)
Next itm

RX = Left(RX, Len(RX) - 1)

End Function

 

[hagia_sofia]

Another way:

=MAP(B2:B4,LAMBDA(array,
LET(
a,TRANSPOSE(TEXTSPLIT(SUBSTITUTE(array,CHAR(10)," ")," ")),
b,a="INV#:",
c,SEQUENCE(ROWS(a))*b,
d,FILTER(c,c<>0)+1,
TEXTJOIN(CHAR(10),,INDEX(a,d,0)))))

 

[kweaver]

I tried this: =TEXTJOIN(CHAR(10),TRUE,TOROW(LEFT(TEXTAFTER(TEXTSPLIT(B2,CHAR(10)),": "),8),2))

with Wrap Text in the column.

 

[LAY2022]

Another way:

=MAP(B2:B4,LAMBDA(array,
LET(
a,TRANSPOSE(TEXTSPLIT(SUBSTITUTE(array,CHAR(10)," ")," ")),
b,a="INV#:",
c,SEQUENCE(ROWS(a))*b,
d,FILTER(c,c<>0)+1,
TEXTJOIN(CHAR(10),,INDEX(a,d,0)))))

Thank you, I will try this.

 

[LAY2022]

I tried this: =TEXTJOIN(CHAR(10),TRUE,TOROW(LEFT(TEXTAFTER(TEXTSPLIT(B2,CHAR(10)),": "),8),2))

with Wrap Text in the column.

This is more simple. Thank you so much.

 

[Peter_SSs]

This is more simple. Thank you so much.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.