Finding Specific String within larger String with Qualifications

[Jezzzza]

I have a report showing an audit trail of users and the output is very very messy, no delimiters, massive amount of text in single cell. We have a user who changed the job code field on an unknown number of users, and I am trying to find the last instance it occurred using a particular string and two search parameters , then pulling everything after another string that comes before it.

for example, in this original string:

I am particularly needing the last occurrence of "Changed Job code" but I also need to know the date on which it occurred, so specifically in this example I would need everything from the final green "Updated at..." string to the end but only if it is the "updated at" occurring before the last occurrence of "Changed job code" if that is even possible.

Currently I have this formula:
=IF(AND(COUNTIF(J3,"*Denise*"),"Yes","No",COUNTIF(J3,"*job code*")),"Changed Job code from"&TEXTAFTER(J3,"Changed Job code from",-1),"")

which is working to find all cells that have anything changed by Denise and also including any change in job code, then pulling the data after the last job code change, but I still need to also know on what date it occurred (green highlighted text in image).

Result in column B

Honestly not sure if this is even possible, but if so, it would be a life saver.

Thanks in advance!

 

[Jeffrey Mahoney]

Am I wrong in saying that the "Change Date" comes before the description of the changes? Your result looks like it's showing the change date as 01/30/2025 for the "Changed Job Code". If I read the cell from the top, change dates come first.

 

[Jezzzza]

Hi, yes, the output file has change date first, user who made the change, then lists the changes made, so my current formula is pulling all text after the last "changed job code" string as needed, I just need to also pull back the associated "updated at" string up to and including what my formula is returning.

Thanks!

 

[Jeffrey Mahoney]

This function works for me

Function GetLastChangedJobCode(Cel As Range) As String
  Dim LastCJCPos As Long
  Dim CJCDatePos As Long
  Dim NextBar As Long
  Dim aStr As String
  Dim CJCText As String
  Dim UpdateText As String
  
  aStr = Cel.Value
   
  LastCJCPos = InStrRev(aStr, "Changed Job code")
  If LastCJCPos = 0 Then Exit Function
  NextBar = InStr(LastCJCPos, aStr, "|")
  CJCText = Mid(aStr, LastCJCPos, NextBar - LastCJCPos)
  CJCDatePos = InStrRev(aStr, "Updated at ", LastCJCPos)
  NextBar = InStr(CJCDatePos, aStr, "|")
  UpdateText = Mid(aStr, CJCDatePos, NextBar - CJCDatePos)
  GetLastChangedJobCode = UpdateText & " | " & CJCText
  
  
End Function

Book1
	B	C
2	Updated at 08/27/2024 08:38 AM by Bagwell, Jeremy using the API | Changed User Active? [Custom field] from "" to "1" | Updated at 09/24/2024 12:38 PM by Bagwell, Jeremy | Changed Profiles approver from "Bagwell, Jeremy" to "[Manager]" | Updated at 09/24/2024 01:10 PM by Bagwell, Jeremy using the API | Changed User location from "" to "Raleigh, NC" | Updated at 10/01/2024 04:10 PM by Sullivan, Cahalan | Changed Rate from "0.00" to "381.00" | Changed Job code from "CORP, USA-General and Admin" to "CORP, USA - Business Operations" | Updated at 12/05/2024 12:07 PM by Bagwell, Jeremy | Changed Profiles approver from "[Manager]" to "Bagwell, Jeremy" | Updated at 01/02/2025 12:10 PM by Bagwell, Jeremy | Changed Profiles approver from "Bagwell, Jeremy" to "[Manager]" | Updated at 01/16/2025 06:15 PM by Leip, Denise using the NetSuite integration | Changed Job code from "CORP, USA - Business Operations" to "CORP, USA - General Changed Job code from "CORP, USA- Business and Admin" | Updated at 01/30/2025 03:47 PM by Bagwell, Jeremy using the API | Changed Project Operations" to "CORP, USA - General and Admin" | Manager? [Custom field] from "" to "1" | Updated at 03/19/2025 01:15 AM by Vijayan, Mani using the Updated at 01/30/2025 03:47 PM by Bagwell, Jeremy using NetSuite integration | Changed Legal Last Name [Custom field] from "" to "Sullivan" | Changed Legal First Name [Custom field] from "" to "Cahalan" the API Changed Project Manager? [Custom field] from to "1" | Updated at 03/19/2025 01:15 AM by Vijayan, Mani using the NetSuite integration | Changed Legal Last Name [Custom field] from" to "Sullivan" | Changed Legal First Name [Custom field] from" to "Cahalan"	Updated at 01/16/2025 06:15 PM by Leip, Denise using the NetSuite integration | Changed Job code from "CORP, USA- Business and Admin"
Sheet1

Cell Formulas
Range	Formula
C2	C2	=GetLastChangedJobCode(B2)

 

[Jeffrey Mahoney]

It would be helpful next time if you could post text instead of pictures. XL2BB works great for that.

 

[Jezzzza]

I entered that vba code exactly, saved, and entered that formula, but =GetLastChangedJobCode(A2) as that is where the original string is, and I get the #NAME? error.

 

[Jeffrey Mahoney]

Did you put the code in the same workbook and in a standard module, not a sheet module?

 

[Jezzzza]

Yes, same workbook, I double clicked on the tab name (sheet2) and saved it there, should it go under "ThisWorkbook" ?


I'm post a screenshot but the site isn't letting me saying it is too large.


I'm not super savvy with VBA

 

[Jeffrey Mahoney]

Right click on Modules and choose INSERT > Module. Double click on the new module and past the code in the window to the right.

 

[Jeffrey Mahoney]

Actually, right click on any of those objects to get the same result