Appending text to a certain section in a cell (VBA)

[rimrattlerla11]

I have a large dataset in a csv file in which I am trying to append text whenever it comes across the words "PREFERRED CLASSROOM PKG." in the cell. Currently the word "null" is found where I want the text to appear.

The PREFERRED CLASSROOM PKG. text appears in the beginning or somewhere in the middle of the cell.
Each cell of the worksheet is in a format similar to below:

[{"code":null,"long_name":null,"name":"PREFERRED CLASSROOM PKG.101A","msrp":100,"includes": ]},"code":null,"long_name":null,"name":"ABCDEF","msrp":80,"includes":[]},.......]

OR

[{"code":null,"long_name":null,"name":"ABCDEF","msrp":80,"includes": ]},"code":null,"long_name":null,"name":"PREFERRED CLASSROOM PKG.101A","msrp":100,"includes":[]},.......]



The desired output inside each cell will be replacing the word null with the following two things:

1. Appending the four characters that come after the words "PREFERRED CLASSROOM PKG." to the "code" section surrounded by " " ("101A")
2. Appending the words Classroom Group and the four character code to the "long_name" section surrounded by " ". ("Classroom Group 101A")

[{"code":"101A","long_name":"Classroom Group 101A","name":"PREFERRED CLASSROOM PKG.101A","msrp":100,"includes": ]},"code":null,"long_name":null,"name":"ABCDEF","msrp":80,"includes":[]},.......]

OR

[{"code":null,"long_name":null,"name":"ABCDEF","msrp":80,"includes": ]},"code":"101A","long_name":"Classroom Group 101A","name":"PREFERRED CLASSROOM PKG.101A","msrp":100,"includes":[]},.......]

Please let me know if anything was unclear.
I'm out of my element with VBA, and I'm fairly certain I need some VBA code in order to make this happen.

Thanks in advance for any help you can provide!

 

[Peter_SSs]

Try this macro in a copy of your workbook. Post back if you need details of how to implement it.

I have assumed data starts in cell A2 and I've written the results starting in B2. You can try changing the code (see red items) if that is not the correct layout or post back with those details.

Sub Replace_Text()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String, sSub1 As String, sSub2 As String
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(.+)(null)(.+?)(null)(.+?PREFERRED CLASSROOM PKG.+)"
  For i = 1 To UBound(a)
    s = a(i, 1)
    sSub1 = Mid(s, InStr(1, s, "PREFERRED CLASSROOM PKG.") + 24, 4)
    sSub2 = "Classroom Group " & sSub1
    a(i, 1) = RX.Replace(s, "$1""" & sSub1 & """$3""" & sSub2 & """$5")
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub