Delete Text from String

[badinvestor]

Hi all,
I have what I think is a simple question but can't quite get it.
I have the following table and 700+ rows, what I'm looking to do is either by macro or formula find a way to remove all the chars ACDRUVQ from col E and have remaining char(s) put into col F. I have tried a combo of =MID and =FIND such as =MID(E20,FIND("A",E20,1)+3,9) but can't get exactly what I need since the char. can be in any position in the string. Any help would be great!

Excel Workbook
	A	B	C	D	E	F
5	System Code	Form ID	Token	Form Name	Available Function Codes	Granted Function Codes or Y for access
19	AM	AM06	AM06.4	Asset Class Depreciation Rates	+-ACI	to remove ACDRUVQ
20	AM	AM06	AM06.5	Asset Class Depreciation Rate Detail	+-ACDINP	to remove ACDRUVQ
21	AM	AM06	AM06.6	Depreciation Allocation	+-ACDINPRU	to remove ACDRUVQ
22	AM	AM07	AM07.1	Location	NPACID	to remove ACDRUVQ
AmInquiry

Excel 2003

 

[GTO]

Presuming you want results like in Col F

Excel Workbook
	A	B	C	D	E	F
1	System Code	Form ID	Token	Form Name	Available Function Codes	Granted Function Codes or Y for access
2	AM	AM06	AM06.4	Asset Class Depreciation Rates	+-ACI	+-I
3	AM	AM06	AM06.5	Asset Class Depreciation Rate Detail	+-ACDINP	+-INP
4	AM	AM06	AM06.6	Depreciation Allocation	+-ACDINPRU	+-INP
5	AM	AM07	AM07.1	Location	NPACID	NPI
6
Sheet1

Excel 2003

In a Standard Module:

Option Explicit
    
Sub exa()
Dim REX As Object
Dim wks As Worksheet
Dim rngData As Range
Dim Cell As Range
    
    Set REX = CreateObject("VBscript.RegExp")
    With REX
        .Global = True
        .IgnoreCase = False
        .Pattern = "[ACDRUVQ]"
        
        Set wks = ThisWorkbook.Worksheets("Sheet1")
        With wks
            Set rngData = Range(.Cells(2, "E"), .Cells(.Rows.Count, "E").End(xlUp))
        End With
        
        For Each Cell In rngData
            If .Test(Cell.Value) Then
                Cell.Offset(, 1).Value = .Replace(Cell.Value, vbNullString)
            End If
        Next
    End With
End Sub

Hope that helps,

Mark

 

[badinvestor]

Amazing! Works perfect, thanks!

 

[GTO]

Amazing! Works perfect, thanks!

You are very welcome