Extract multiple orders from a cell with a large text string

[makubexho]

Hi There,

I need to extract order numbers from a cell that contains a large text string, like in cell A1 containing “AP13215hsgyh),AP13333,)218)gsjebbjAP13376/()827)?AP13222……” AP+5 digits are all orders that I need to extract from this cell, which may contain 20 orders at a time.

Any thought?

Thanks ahead

 

[Fluff]

Thanks for updating your profile & for the sample data.

i'd rather use these functions or formulae than VBA code that I am not familiar.

As you are using xl2016 you do not have the Textjoin function, so VBA will be your only realistic option

 

[mohadin]

As function

Function orders(r As Range)
Dim z As Object
Dim i As Long
Dim out As String
With CreateObject("VBScript.RegExp")
  .Pattern = "(AP\d+)"
  .Global = True
  out = ""
Set z = .Execute(a)
  For i = 1 To z.Count
 out = IIf(out = "", z.Item(i - 1) & ",", out & z.Item(i - 1) & ",")
  Next
orders = """" & out & "...(" & z.Count & ")" & "Orders"""
End With
End Function

=orders (A1)

 

[mohadin]

As function

Function orders(r As Range)
Dim z As Object
Dim i As Long
Dim out As String
With CreateObject("VBScript.RegExp")
  .Pattern = "(AP\d+)"
  .Global = True
  out = ""
Set z = .Execute(a)
  For i = 1 To z.Count
 out = IIf(out = "", z.Item(i - 1) & ",", out & z.Item(i - 1) & ",")
  Next
orders = """" & out & "...(" & z.Count & ")" & "Orders"""
End With
End Function

=orders (A1)