How to extract multiple numbers from one cell, to multiple rows?

[user47]

Good day,

I have a dataset with a column of thousands of rows of invoice numbers.
Sometimes the invoice numbers appears once per row, sometimes multiple invoice numbers appear in one row (always in the same column).
I can only manage to extract the first instance of the invoice number in all cases.
Is there any way to do for the cases where they appear multiple times (beside manually)?

Thank you.

Sample Data.xlsx
	A
2	(data is messy like this this)
3	Invoices
4	Inv. 1234, Inv. 12345
5	Inv. 4569
6	Inv 6467, Inv. 3835, Inv. 4786
7	Inv.5672, Inv. 3199, Inv.41165
8
9
10	(need to get like this)
11	Inv #
12	1234
13	12345
14	4569
15	6467
16	3835
17	4786
18	5672
19	3199
20	41165
Sheet2

 

[kweaver]

Welcome to Mr. Excel. Please update your profile to indicate which version of Excel you run.

 

[user47]

Welcome to Mr. Excel. Please update your profile to indicate which version of Excel you run.

Thank you. Just did- office 365

 

[shift-del]

Hi

I would go for PQ.

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Invoices"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"Inv","",Replacer.ReplaceText,{"Invoices"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".","",Replacer.ReplaceText,{"Invoices"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," ","",Replacer.ReplaceText,{"Invoices"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"Invoices", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Invoices"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Invoices", Int64.Type}})
in
    #"Changed Type"

 

[kweaver]

I played w/a macro with the results in the D column:

Sub SplitInv()
Dim str() As String, str1 As String, str2() As String, i As Long, j As Long, k As Long
Dim lr As Long, size As Integer, sz As Integer
size = 0
sz = 3
lr = Cells(Rows.Count, "A").End(xlUp).Row
For k = 3 To lr
str1 = Trim(Replace(Cells(k, "A"), "Inv. ", ""))
str2 = Split(str1, ",")
size = sz + UBound(str2)
j = 0
For i = sz To size
Cells(i, "D") = str2(j)
j = j + 1
Next i
sz = sz + 1 + UBound(str2)
Next k
End Sub

 

[Peter_SSs]

You could also try this single formula in a single cell.

22 02 13.xlsm
	A
1
2	(data is messy like this this)
3	Invoices
4	Inv. 1234, Inv. 12345
5	Inv. 4569
6	Inv 6467, Inv. 3835, Inv. 4786
7	Inv.5672, Inv. 3199, Inv.41165
8
9
10	(need to get like this)
11	Inv #
12	1234
13	12345
14	4569
15	6467
16	3835
17	4786
18	5672
19	3199
20	41165
21
Extract Numbers

Cell Formulas
Range	Formula
A12:A20	A12	=LET(all,CONCAT(A4:A7),one,MID(all,SEQUENCE(LEN(all)),1),FILTERXML("<p><c>"&SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(one+0),one," ")))," ","</c><c>")&"</c></p>","//c"))
Dynamic array formulas.

 

[Peter_SSs]

If a macro approach is preferred, here is another option, with results in column B

Sub GetInvNumbers()
  Dim RX As Object
  Dim Nums As Variant
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\D"
  Nums = Split(Application.Trim(RX.Replace(Join(Application.Transpose(Range("A4", Range("A4").End(xlDown)))), " ")))
  Range("B2").Resize(UBound(Nums) + 1).Value = Application.Transpose(Nums)
End Sub

22 02 13.xlsm
	A	B
1
2	(data is messy like this this)	1234
3	Invoices	12345
4	Inv. 1234, Inv. 12345	4569
5	Inv. 4569	6467
6	Inv 6467, Inv. 3835, Inv. 4786	3835
7	Inv.5672, Inv. 3199, Inv.41165	4786
8		5672
9		3199
10		41165
11
Extract Numbers (vba)

 

[Peter_SSs]

I have a dataset with a column of thousands of rows of invoice numbers.

I overlooked that in my responses. Depending on how many "thousands" are involved and how many invoices could appear in each row, either of my suggestions could possibly fail - or need modification.

 

[user47]

Thank you everyone so much for your responses.

I learnt a lot delving more into your solutions. I found user's "Shift-del" solution to be the simplest so far in power query using an index column as well.

Will mark this as solved.

Thanks again to all.

 

[Peter_SSs]

Glad you got a Successful outcome. Thanks for letting us know.