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

 

[mohadin]

Hi
Try
for range A1 result in B

Sub test()
Dim z As Object
Dim i As Long
With CreateObject("VBScript.RegExp")
  .Pattern = "(AP\d+)"
  .Global = True
Set z = .Execute(Range("a1"))
  For i = 1 To z.Count
   Range("b" & i) = z.Item(i - 1)
  Next
End With
End Sub

 

[mumps]

Try this version. It assumes your strings are in column A starting in row 1. The results will be placed to the right of each string in separate columns.

Sub GetSubStrings()
    Dim rgxMatch As Object, rgxMatches As Object, strPattern: strPattern = "AP\d{5}"
    Dim x As Long: x = 1
    Dim y As Long: y = 2
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = strPattern
        Do
            Set rgxMatches = .Execute(Cells(x, 1).Value)
            For Each rgxMatch In rgxMatches
                Cells(x, y).Value = rgxMatch.Value
                y = y + 1
            Next
            y = 2
            x = x + 1
        Loop Until IsEmpty(Cells(x, 1))
    End With
End Sub

 

[NdNoviceHlp]

Hi makubexho. It's Sunday am and it seems like some mental gymnastics would clear the fog. So here's my contribution. You didn't state where U wanted the output so I just put them in the adjacent columns. HTH. Dave

Sub AP5(InputRng As Range)
Dim TempStr As String, Temp As Variant, Cnt As Integer
Dim Position As Integer, OutStr As String
TempStr = CStr(InputRng)
Temp = Split(TempStr, "AP")
For Cnt = LBound(Temp) + 1 To UBound(Temp)
Position = InStr(TempStr, Temp(Cnt)) - 2
OutStr = Right(Left(TempStr, Len(Right(TempStr, Position + 6))), 7)
Range(InputRng.Address).Offset(0, Cnt) = OutStr
Next Cnt
End Sub

To operate...

Call AP5(Sheets("Sheet1").Range("A" & 1))

 

[makubexho]

Thanks those that already contribute their thoughts and solutions on my excel puzzle.

However, let me further extend on details of my puzzle encounter in this situation:

In column A and row 1, I have this large text string (see screen shot below) that contains a series of orders (AP13557, AP13556...) that is seperated by unwanted characters the brackets, comma, text strings, semi colon.

the end result that i want should look like this in one single cell A2 (in a array form that i prefer): "AP13557,AP13556,AP13516,AP13550,AP13519....(20 orders)"

Someone suggested I give it a go with the 'Text to Column' thats in built in excel - that however does not give the effect i want - that i want all orders to be extracted into one single cell (A2) as stated above.

I also tried to use the fuctions Mid, Len, Search, Find, and a combination of them. I did not succeed (more complicated than i thought). if possible, i'd rather use these functions or formulae than VBA code that I am not familiar.

 

[Fluff]

What version of Excel are you using & what platform?

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

 

[Fluff]

Also can you post an example of one of your strings.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[makubexho]

Also can you post an example of one of your strings.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the hint

 

[makubexho]

data.xlsx
	A
1	PUSAN C MA149A 014IP1233425 (Your Ref: AP13587), 014IP1233426 (Your Ref: AP13580), 014IP1233428 (Your Ref: AP13578), 014IP1233434 (Your Ref: AP13586), 014IP1233629 (Your Ref: AP13622)
Sheet2

pleases see updated mini-sheet

 

[mohadin]

data.xlsx
	A
1	PUSAN C MA149A 014IP1233425 (Your Ref: AP13587), 014IP1233426 (Your Ref: AP13580), 014IP1233428 (Your Ref: AP13578), 014IP1233434 (Your Ref: AP13586), 014IP1233629 (Your Ref: AP13622)
Sheet2

Sub test()
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(Range("a1"))
  For i = 1 To z.Count
 out = IIf(out = "", z.Item(i - 1) & ",", out & z.Item(i - 1) & ",")
  Next
Range("A2") = """" & out & "...(" & z.Count & ")" & "Orders"""
End With
End Sub