Solved

Replace function - Excel

Posted on 2011-09-12
6
167 Views
Last Modified: 2012-05-12
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
Comment
Question by:Karl001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
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
0
 
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.
 as
=REPLACE(A1,FIND("o",A1,1),1,"t") will replace o with t.
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36522389
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!

 
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)
    Loop
    End If
    Next i
End Sub
0
 

Accepted Solution

by:
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

by:Karl001
ID: 36553424
find solution
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

688 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