Improve company productivity with a Business Account.Sign Up


Replace function - Excel

Posted on 2011-09-12
Medium Priority
Last Modified: 2012-05-12
How can I replace, in a column, a caracter by an other one.


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

Expert Comment

by:Shanmuga Sundaram
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
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36522378
sorry this should be
=REPLACE(A1,FIND("o",A1,1),3,"t") will replace o with t.
=REPLACE(A1,FIND("o",A1,1),1,"t") will replace o with t.
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36522389
The best way i would recommend is to go for a macro
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 17

Expert Comment

by:Shanmuga Sundaram
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)
    End If
    Next i
End Sub

Accepted Solution

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

Selection.Replace What:="YourName?", _
                               Replacement:=",Question,", LookAt:=xlPart , _
                               SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Author Closing Comment

ID: 36553424
find solution

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Here is why.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question