'========================================================================================
'- FIND AND REPLACE TEXT (white space) IN CELLS
'- Uses Regular Expressions
'- This version removes white space from cells in column A
'- ** For "safety" and testing this puts results into column B ***
'- ** Change the code below to replace column A ***
'========================================================================================
'- Brian Baulsom August 2010
'========================================================================================
Sub CELL_TEXT_REPLACE()
Dim MyRegExp As Object
Dim MyString As String
Dim NewString As String
Dim MyRow As Long
Dim LastRow As Long
'========================================================================================
'- REGULAR EXPRESSION PATTERN TO FIND TEXT TO REPLACE
Const MyPattern As String = "\t|\n"
'Const MyPattern As String = "\s" ' this version removes intervening spaces too
'========================================================================================
Set MyRegExp = CreateObject("VbScript.RegExp")
Application.Calculation = xlCalculationManual
'------------------------------------------------------------------------------------
'- LOOP WORKSHEET ROWS
LastRow = Range("A65536").End(xlUp).Row
For MyRow = 2 To LastRow
Application.StatusBar = MyRow - 1 & " / " & LastRow - 1
MyString = Cells(MyRow, "A").Value
'-------------------------------------------------------------------------------
'- EXECUTE REGULAR EXPRESSION REPLACE
With MyRegExp
.Global = True
.pattern = MyPattern
.ignorecase = True
NewString = .Replace(MyString, "")
End With
'-------------------------------------------------------------------------------
'- RESULTS TO CELL
'-------------------------------------------------------------------------------
' Cells(MyRow, "A").Value = NewString ' replaces text in the cell
Cells(MyRow, "B").Value = NewString ' result to column B
MyString = ""
NewString = ""
Next
'-----------------------------------------------------------------------------------
MsgBox ("Done")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'========== EOP ========================================================================