Split cell contexts into multiple cells based on characters and repalce text with ""

[bjtys]

My first post please, be patient with me. I searched the posts and found this article helpful but it doesn't offer a complete solution. https://www.mrexcel.com/forum/excel...remove-specific-text-cells-within-column.html.

I have 160,000+ rows in column C containing a URI for which I need to remove portions of the URI and\or split it into 2 columns.
Column C contains varying strings like the following (w\o quotes):

"/case/Default.aspx?pageId=1212"
"/case/Default.aspx?pageId=1085&ArtRef=US-WEL-19"
"/case/Default.aspx?PageId=1085&secureTId=b99itrG6PlV8GmmTO+cyHA=="

What I'd like to do (using VBA) is:
if cell content is like "/case/Default.aspx?pageId=1212" (without quotes) remove everything prior to and including = leaving just the 1212. In all cases the text prior to the number is "/case/Default.aspx?pageId="

if cell content is like "/case/Default.aspx?pageId=1085&ArtRef=US-WEL-19" (without quotes) split into 2 cells leaving 1085 in existing cell and copying US-WEL-19 to cell directly to the right (in this case D). The text after "ArtRef=" varies. Column C would contain 1085 and column D would contain US-WEL-19.

if cell content is like "/case/Default.aspx?PageId=1085&secureTId=b99itrG6PlV8GmmTO+cyHB==" remove everything except the 1085. There are various cells where the text after the number varies. I only care about splitting it if it contains something like "ArtRef=US-WEL-19". If it is anything else I just want to keep the number after pageID=.

Thanks in advance,
bjtys

 

[Kaper]

Try such macro.
I'm not sure (but could be) if it would work fine for 160K+ rows, but for sure shall work rather quickly for reasonable size sets.
So please try it on growning sets of data.
And if only size would appear to be a problem, we will work on doing the same part by part.

Sub MySplitMacro()

    Dim lastRow As Long, i As Long
    Dim myarr, splittedrow
    
'   Find lastRow in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

'   copy range to look from column A and empty column B into array
    myarr = Range("B1:A" & lastRow).Value
    
' for each row in array
    For i = 1 To UBound(myarr)
' split at &
      splittedrow = Split(myarr(i, 1), "&")
' first element split at = and tahe what is after that into first output column
      myarr(i, 1) = Split(splittedrow(0), "=")(1)
' if there is second part and it starts with ArtRef=
' split this part at = and take second part into second column
      If UBound(splittedrow) = 1 Then _
        If splittedrow(1) Like "ArtRef=*" Then _
          myarr(i, 2) = Split(splittedrow(1), "=")(1)
    Next i
' paste array into sheet (for instance in columns C and D)
    Range("C1").Resize(UBound(myarr), 2).Value = myarr
End Sub

 

[bjtys]

Thanks Kaper this worked very well. So well it made me realize I missed something. In my original post I stated "if cell content is like "/case/Default.aspx?pageId=1212" (without quotes) remove everything prior to and including = leaving just the 1212. In all cases the text prior to the number is "/case/Default.aspx?pageId=""

This is incorrect. After running the VBA I realized there are cells with text where the pageID=[some number] comes after the "&". Here is an example "/case/default.aspx?token=00e55c8e-f021-4e54-9a1c-7cbcf89b0553&pageId=1078". After running the code I ended up with "00e55c8e-f021-4e54-9a1c-7cbcf89b0553" in the column where I was expecting to have the numbers following pageID "1078" and column D was blank.

I tried to modify your code but clearly you are way smarter than me.

My apologies for missing this in my original post.

 

[Kaper]

Nothing to do with smartness, I just know the tool somewhat better

Try:

Sub MySplitMacro()


    Dim lastRow As Long, i As Long, j As Long
    Dim myarr, splittedrow
    
'   Find lastRow in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row


'   copy range to look from column A and empty column B into array
    myarr = Range("B1:A" & lastRow).Value
    
' for each row in array
    For i = 1 To UBound(myarr)
      j = InStr(1, CStr(myarr(i, 1)), "pageId=", vbTextCompare)


' split at & part after pageID
      splittedrow = Split(Mid(CStr(myarr(i, 1)), j + 7), "&")
' first element split at = and tahe what is after that into first output column
      myarr(i, 1) = splittedrow(0)
' if there is second part and it starts with ArtRef=
' split this part at = and take second part into second column
      If UBound(splittedrow) = 1 Then _
        If splittedrow(1) Like "ArtRef=*" Then _
          myarr(i, 2) = Split(splittedrow(1), "=")(1)
    Next i
' paste array into sheet (for instance in columns C and D)
    Range("C1").Resize(UBound(myarr), 2).Value = myarr
End Sub

 

[bjtys]

Perfect. Thank you so much Kaper. If I ever get to Warsaw, Poland I will buy you a beer or 12.

 

[Kaper]