Find and Remove Specific Text with VBA

[Data_Crunched]

Hi all, new to the group, but will do my best to make this as clear as possible. I searched up and down and couldn't find a solution to this problem.

I have a macro that searches for specific trades that are listed within cells of an Excel spreadsheet and replaces the exported craft names with shortened versions (eg. Carpenter is shortened to CARP, Electrician is shortened to ELECT, etc.). The number in parenthesis is the quanity of each craft required to perform the job, and it exports into Excel like that from our EPR database. Many work orders require multiple crafts, which is why in my example below, you'll see multiple crafts listed in one cell, delineated by a comma. I'd like the macro to completely remove specific crafts if possible (Manufacturing Engineer, Electrical Engineer, Operations Coordinator, Facility Manager), there are other crafts I want to remove, but as long as I can see an example of how one is done, I can duplicate it.

Here's an example of the macro I'm working with, and it seems to work well with everything I'm doing(i.e. search and replace), but I can't figure out how to modify it to remove, delete or ignore specific crafts completely. I only pasted the top third of the macro as it's quite long and didn't feel it was necessary beyond the area in red font, which is what I mostly modify for search and replace.


Dim fromdateuserinput As String 'This variable will store the beginning date of the range of dates that will be kept in the data; it will be input by the user
Dim throughdateuserinput As String 'This variable will store the end date of the range of dates that will be kept in the data; it will be input by the user

Do Until IsDate(fromdateuserinput) = True 'This loop will continue until a date is captured with correct syntax, or until the user cancels the macro entirely
fromdateuserinput = InputBox("Please input the beginning date of the range of dates you desire to show in the Schedule (format mm/dd/yy)", _
"Input From Date", Format(Now(), "mm/dd/yy")) 'The InputBox creates a pop-up that will prompt the user for the beginning of the date range of WOs to be captured

If IsDate(fromdateuserinput) Then 'Checks if the input string from the user is a date
fromdateuserinput = Format(CDate(fromdateuserinput), "mm/dd/yy Hh:Nn:Ss AM/PM") 'If the string is a date, the formatting of it is changed to include hours, minutes, and seconds (for convenience while working in VBA)
ElseIf fromdateuserinput = "" Then 'If the string is empty, then the macro is cancelled. The string is always empty if the user cancels/closes the pop-up
MsgBox "Macro has been cancelled; user input was null."
Exit Sub
Else
MsgBox "Wrong date format...Try Again" 'If the string input by the user was not formatted correctly, the user will be informed, and the loop will repeat, prompting the user again for a date
End If
Loop

Do Until IsDate(throughdateuserinput) = True 'This loop will continue until a date is captured with correct syntax, or until the user cancels the macro entirely
throughdateuserinput = InputBox("Please input the ending date of the range of dates you desire to show in the Schedule (format mm/dd/yy)", _
"Input Through Date", Format(Now(), "mm/dd/yy")) 'The InputBox creates a pop-up that will prompt the user for the end of the date range of WOs to be captured

If IsDate(throughdateuserinput) Then 'Checks if the input string from the user is a date
throughdateuserinput = Format(CDate(throughdateuserinput), "mm/dd/yy Hh:Nn:Ss AM/PM") 'If the string is a date, the formatting of it is changed to include hours, minutes, and seconds (for convenience while working in VBA)
ElseIf throughdateuserinput = "" Then 'If the string is empty, then the macro is cancelled. The string is always empty if the user cancels/closes the pop-up
MsgBox "Macro has been cancelled; user input was null."
Exit Sub
Else
MsgBox "Wrong date format" 'If the string input by the user was not formatted correctly, the user will be informed, and the loop will repeat, prompting the user again for a date
End If
Loop

throughdateuserinput = DateAdd("h", 23, throughdateuserinput) '11 hours, 59 minutes, and 59s are added to the end date that the user input so that logic will correctly below
throughdateuserinput = DateAdd("n", 59, throughdateuserinput) 'For example, if the user input the date 5/7/2018 as the end date, this code will make sure that the logic below keeps data/WOs
throughdateuserinput = DateAdd("s", 59, throughdateuserinput) 'with schedule dates on 5/7/18 itself


Dim lastrowindex As Long
lastrowindex = Cells(Rows.Count, "A").End(xlUp).Row 'lastrowindex = number of rows of data on the sheet. It is based off the Status column (currently column F) because the status field always contains data when exporting

ActiveSheet.Range("A3:M" & lastrowindex).RemoveDuplicates Columns:=2, Header:=xlYes 'Remove Duplicates based on Work Order number data field. I replaced the fixed range that the Macro recorded with ("A1:J" & lastrowindex) which identifies all the data

lastrowindex = Cells(Rows.Count, "A").End(xlUp).Row 'reset the lastrowindex as number of rows may have decreased with the duplicates removed


Range("J4:J" & lastrowindex).Select 'Replacment in Periodicity column
Selection.Replace What:="NOT APPLICABLE", Replacement:=" ", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Range("K4:K" & lastrowindex).Select
Selection.Replace What:="Escort Required", Replacement:="ESCORT", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Cal Lab(1)", Replacement:="WSVS[1]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Cal Lab(2)", Replacement:="WSVS[2]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Contamination Control Technician", Replacement:="CCT", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Carpenter(1)", Replacement:="CARP[1]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Carpenter(2)", Replacement:="CARP[2]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Here are the row outputs, column K header is 'Resources' and this would be an example of what would appear in cells K1:K10:

Row 1 ET(4), EE(2), OC(4), DRVR(2), INSP(4), MT(10), SAF(1), FLTMECH(1), ME(5)

2 FAB(1), WELD(1), PAINT(1)

3 EE(1), INSP(1), MACH(1)

4 FAB(1), ELECT(1)

5 FAB(1), INSP(1)

6 FAB(2), WELD(1), INSP(1), MT(2), MACH(1)

7 CARP(2)

8 FLTMECH(1)

9 ET(2), EE(1), OC(1), MT(2), TBMCI(1), ME(4)

10 OC(1), INSP(1), MT(1), ME(1)

<colgroup><col style="mso-width-source:userset;mso-width-alt:19968;width:410pt" width="546"> </colgroup><tbody>
</tbody>

In the outputs listed above, I'd like to remove any instance of ME, EE, OC




Thanks in advance for your help!

 

[Data_Crunched]

Sorry, my table didn't appear how I had expected it to, this is a better representation:

ET(4), EE(2), OC(4), DRVR(2), INSP(4), MT(10), SAF(1), FLTMECH(1), ME(5)

FAB(1), WELD(1), PAINT(1)

EE(1), INSP(1), MACH(1)

FAB(1), ELECT(1)

FAB(1), INSP(1)

FAB(2), WELD(1), INSP(1), MT(2), MACH(1)

CARP(2)

FLTMECH(1)

ET(2), EE(1), OC(1), MT(2), TBMCI(1), ME(4)

OC(1), INSP(1), MT(1), ME(1)

<colgroup><col></colgroup><tbody>
</tbody>

 

[Fazza]

This not very elegant, as explained in the comments. HTH

Sub ExampleOfHwoToCallSubThatDoesTheWork()


    Call RemoveData(TargetRange:=Range("K1:K10"), ListOfCodes:=Array("EE", "ME", "OC"))


End Sub


'Data to be deleted is something like "EE(1)" or sometimes has a trailing comma as in "EE(3), "
'Quick and dirty approach:
'   Loop through for each identifying string ("EE", "ME", "OC", etc)
'       And for each loop through a counter 1 to some maximum
'           doing find "EE(n), " & replace
'           and also find "EE(n)" & replace
Sub RemoveData(ByRef TargetRange As Excel.Range, ByVal ListOfCodes As Variant)
    Const MAXIMUM_COUNT As Long = 30 'CHANGE THIS IF REQUIRED
    Dim iCode As Long
    Dim iCount As Long
    
    Application.ScreenUpdating = False
        
    'https://www.mrexcel.com/forum/excel-questions/1100748-find-remove-specific-text-vba.html
    'Sample pasted data had CHR(160) characters in places, instead of normal CHR(32) spaces
    'Change any CHR(160) spaces to normal CHR(32) spaces
    TargetRange.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
    
    For iCode = LBound(ListOfCodes) To UBound(ListOfCodes)
        For iCount = 1 To MAXIMUM_COUNT
            'Ends in a comma & not very first part of cell, or very last
            TargetRange.Replace What:=", " & ListOfCodes(iCode) & "(" & iCount & "), ", Replacement:=", "
            'Ends in a comma & is very first part of cell
            TargetRange.Replace What:=ListOfCodes(iCode) & "(" & iCount & "), ", Replacement:=""
            'Does not end in a comma. (So, is at end of cell's contents)
            TargetRange.Replace What:=", " & ListOfCodes(iCode) & "(" & iCount & ")", Replacement:=""
            'Or if very first part of cell
            TargetRange.Replace What:=ListOfCodes(iCode) & "(" & iCount & ")", Replacement:=""
        Next iCount
    Next iCode
End Sub

 

[Data_Crunched]

Thanks Fazza for taking the time to work on this problem!

The code partially worked for me, the craft descriptions were removed but the number in parenthesis stayed, as did the commas.

I did a little adjusting of my original macro to find the craft names and replace with “”, which removes the craft name and the number that follows it, but leaves the commas…

I understand that because I’m replacing text with “”(nothing), the ‘nothing’ is still being recognized as ‘something’, which is why the commas are still showing up.

Is it possible to remove the additional commas?

See screenshots for before and after.


Before:

Electronic Technician(4), Electrical Engineer(2), Operations Coordinator(4), Inspector(4), Driver(2), Mechanical Technician(10), Safety(1), Fleet Mechanic(1), Mechanical Engineer(5)

Electrical Engineer(1), Inspector(1), Machinist(1)

Electronic Technician(1), Electrical Engineer(1), Inspector(1)

Electronic Technician(1), Inspector(1), Mechanical Technician(2), Mechanical Engineer(1)

Operations Coordinator(1), Inspector(1), Mechanical Technician(1), Mechanical Engineer(1)

Electronic Technician(2), Electrical Engineer(1), Operations Coordinator(1), Mechanical Technician(2), Logistics(1), Mechanical Engineer(4)

Electronic Technician(1), Field Engineer(2)

Electrical Engineer(1), Electrician(2)

Electrical Engineer(1), Electrician(1)

<colgroup><col></colgroup><tbody>
</tbody>

After:

ET[4], , , INSP[4], DRVR(2), MT[10], SAF[1], FLTMECH[1],

, INSP[1], MACH[1]

ET[1], , INSP[1]

ET[1], INSP[1], MT[2],

ET[2], , , MT[2], TBMCI[1],

, INSP[1], MT[1],

ET[1],

, ELECT[2]

, ELECT[1]

<colgroup><col></colgroup><tbody>
</tbody>

Ideal state:

ET[4], INSP[4], DRVR(2), MT[10], SAF[1], FLTMECH[1]

INSP[1], MACH[1]

ET[1], INSP[1]

ET[1], INSP[1], MT[2]

ET[2], MT[2], TBMCI[1]

INSP[1], MT[1]

ET[1]

ELECT[2]

ELECT[1]

<colgroup><col></colgroup><tbody>
</tbody>

 

[Fazza]

The code needs to be run against the sample data (post #1 ).

I copied & pasted the sample data from post #1 to Excel. The code I posted worked on that. And it removed unwanted parentheses & commas: as far as I could tell, it worked perfectly.

Please check against the sample data from post #1 . If you get a different result from me, there is an issue.

PS. I totally ignored your code. Didn't look at it or copy it or use it. I copied the sample data to Excel and created code to do the find & remove. If your code has already modified that data from the sample posted, that would explain why my code didn't do what I was expecting. If so, please incorporate my code into your code to suit.