VBA RegEx to find specific characters and remove them from range of cells

[clabulis]

Hi,

I've searched through many threads on this board but can't seem to find what I'm specifically looking for. I'm using Excel 2010 on Windows 7 and I have several columns of text (columns A, C, D, F, I, N) on "Sheet1" with cells that could look like the following (text in each cell will vary... so the below is just an example):

'test
''test
' test
' 'test

I would like the cells to become:

test
test
test
test

and I already have some RegEx that can do this: [\w]+((\s|')?)

Each column will always have the same number of rows as each other, but in general, the number of rows will vary. I'm just looking for a sub to run that can make this change for me.

Any help will be appreciated!

 

[ParamRay]

.
.

Try the following code:

Sub AlphaNumeric()

    Dim WRng As Range
    Dim Cell As Range
    Dim Char As String
    Dim Temp As String
    Dim i As Byte
    
    'Set range to work with
    With Worksheets("Sheet1")
        Set WRng = Union( _
            .Columns("A"), _
            .Columns("C"), _
            .Columns("D"), _
            .Columns("F"), _
            .Columns("I"), _
            .Columns("N"))
        Set WRng = WRng.SpecialCells( _
            Type:=xlCellTypeConstants, _
            Value:=xlNumbers + xlTextValues)
    End With
    
    'Set allowed characters
    Char = "[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ]"
    
    Application.ScreenUpdating = False
    
    'Loop through cells and
    'remove disallowed characters...
    
    For Each Cell In WRng
        Temp = vbNullString
        For i = 1 To Len(Cell.Value)
            If UCase(Mid(Cell.Value, i, 1)) Like Char Then
                Temp = Temp & Mid(Cell.Value, i, 1)
            End If
        Next i
        Cell.Value = Temp
    Next Cell

    Application.ScreenUpdating = True

End Sub

 

[mole999]

as a formula for A3 i would have created this

=TRIM(SUBSTITUTE(A3,"'",""))

and then recorded it into a macro

 

[clabulis]

Thanks gpeacock and mole999 for the quick responses, but i guess I should clarify a little more:

All apostrophes that exist in the middle of some text in a cell should be kept. So:

' 'I'llGiveAnExample

should become:

I'llGiveAnExample (Notice the apostrophe in "I'll" is still here)


Also, Peacock, your code seems to leave some leading apostrophes. I notice it in some cases of text in this format: ' 'test became 'test

 

[iliauk]

gpeacock I liked your code a lot!

If you don't mind; a slight alteration to address the last post

 Dim WRng As Range
    Dim Cell As Range
    
    'Set range to work with
    With Worksheets("Sheet1")
        Set WRng = Union( _
            .Columns("A"), _
            .Columns("C"), _
            .Columns("D"), _
            .Columns("F"), _
            .Columns("I"), _
            .Columns("N"))
        Set WRng = WRng.SpecialCells( _
            Type:=xlCellTypeConstants, _
            Value:=xlNumbers + xlTextValues)
    End With
    
    'Set allowed characters
    Char = "[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ]"
   
    For Each Cell In WRng
        Do Until UCase(Left(Cell.Value, 1)) Like Char
                Cell.Value = Right(Cell.Value, Len(Cell.Value) - 1)
        Loop
 
        Do Until UCase(Right(Cell.Value, 1)) Like Char
                Cell.Value = Left(Cell.Value, Len(Cell.Value) - 1)
        Loop
    Next Cell

 

[clabulis]

Thanks Iliauk! Your code seems to work great in a sample file. The strange thing is, it doesn't seem to work in conjunction with my target file with my current code. I would provide my code to maybe help you figure out why, but it contains sensitive information (and it's quite lengthy).

For now, I think maybe I can work with it and come up with a fix.

I'd still happily welcome others to try and help with any alternate solutions though.

 

[clabulis]

Fixed!

Please disregard my previous post. Just had change the location of this piece of code within my own. Thanks again to everyone who helped!