Replace function - Excel

Posted on 2011-09-12
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

839 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