x
Solved

# Replace function - Excel

Posted on 2011-09-12
Medium Priority
175 Views
How can I replace, in a column, a caracter by an other one.

example:

With Worksheets("Question").Range("A:A").Selection
.Replace What:="Your Name?", Replacement:="Question1", LookAt:=xlPart, MatchCase:=False
End With
0
Question by:Karl001
• 4
• 2

LVL 17

Expert Comment

ID: 36522371
Let us assume that in A1 cell we have Welcome. then this formula

=REPLACE(A1,FIND("o",A1,1),3,"t") will replace o with t.

The result will be as Welctme
0

LVL 17

Expert Comment

ID: 36522378
sorry this should be
=REPLACE(A1,FIND("o",A1,1),3,"t") will replace o with t.
as
=REPLACE(A1,FIND("o",A1,1),1,"t") will replace o with t.
0

LVL 17

Expert Comment

ID: 36522389
The best way i would recommend is to go for a macro
0

LVL 17

Expert Comment

ID: 36522423
Public Sub replacechar()
Dim findstring As String
Dim replacestring As String
findstring = "e"
replacestring = "t"
For i = 1 To 100 ' cells from first row to 100 in the column A (A1 to A100)
If Cells(i, 1) <> "" Then
Do Until InStr(1, Cells(i, 1), findstring, vbTextCompare) = 0
Cells(i, 1) = Replace(LCase(Cells(i, 1)), LCase(findstring), LCase(replacestring), 1, 1, vbTextCompare)
Loop
End If
Next i
End Sub
0

Accepted Solution

Karl001 earned 0 total points
ID: 36522497
found solution by usion macro record:

Columns("A:A").Select
Selection.Replace What:="YourName?", _
Replacement:=",Question,", LookAt:=xlPart , _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
0

Author Closing Comment

ID: 36553424
find solution
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.