View Full Version : Solved: Replacing Text
godsowndevil
05-20-2008, 10:18 AM
Hi,
Need to write a code to replace certain characters.
My current code:
Sub Replace_Text
Columns("A:CO").Select
Selection.Replace What:="?", Replacement:="A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
This works fine for most cases, but some of my cells have more than 500-1000 characters & this does not work for those cells. Is there any way to overcome this difficulty?
Simon Lloyd
05-20-2008, 10:29 AM
This works fine for most cases, but some of my cells have more than 500-1000 characters & this does not work for those cells. Is there any way to overcome this difficulty?What version of Excel are you using? you probably have to run the code a second time to catch the others.
godsowndevil
05-20-2008, 10:42 AM
Hi,
I'm using Excel 2003. The replace works row-wise & hence it doesnt go past the cell that has more than 500 characters no matter how many times I run the macro. Also, when I try to put in characters like "Ĝ", VBA does not accept it & gives a "?" instead.
Paul_Hossler
05-21-2008, 05:17 PM
Try this (I used B and Z but works on cells with over 500 chars)
Sub Replace_Text1()
Dim rCell As Range
On Error GoTo NiceExit
For Each rCell In ActiveSheet.Columns("A:CO").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
With rCell
.Value = Application.WorksheetFunction.Substitute(.Value, "B", "Z")
End With
Next
NiceExit:
End Sub
If you want to replace the Unicode characters use ChrW
Sub Replace_Text2()
Dim rCell As Range
On Error GoTo NiceExit
For Each rCell In ActiveSheet.Columns("A:CO").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
With rCell
'U+011C LATIN CAPITAL LETTER G WITH CIRCUMFLEX = 284 decimal
.Value = WorksheetFunction.Substitute(.Value, ChrW(284), "G")
End With
Next
NiceExit:
End Sub
Paul
godsowndevil
05-27-2008, 08:04 AM
Thanks a lot... The Code is working fine...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.