• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Replace function - Excel

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
Karl001
Asked:
Karl001
  • 4
  • 2
1 Solution
 
Shanmuga SundaramCommented:
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
 
Shanmuga SundaramCommented:
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
 
Shanmuga SundaramCommented:
The best way i would recommend is to go for a macro
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Shanmuga SundaramCommented:
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
 
Karl001Author Commented:
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
 
Karl001Author Commented:
find solution
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now