Excel - Find and Replace only the end of a cells text

Asked By ddhargi on 14-Mar-09 12:05 PM
I am looking to do a Find & Replace only the end of a cells texts or add a
'character' to the end of the cell.  ex.

I am trying to place a * at the end of this statement, without clicking in
each cell and adding it at the end.


If I do a find and replace the 'zero', I get - *F*123450*.  I'm trying to
keep the begining of the cell unchanged, and I'm looking for a new user way
of doing it.

Thanks*  :  )

Don Guillett replied on 14-Mar-09 12:37 PM
More before/after examples

Don Guillett
Microsoft MVP Excel
SalesAid Software
Dave Peterson replied on 14-Mar-09 01:48 PM
Can you use a macro?

If yes:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in the range"
For Each myCell In myRng.Cells
myCell.Value = myCell.Value & "*"
Next myCell
End If

End Sub

Select the range to fix first and run it.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:

David McRitchie has an intro to macros:

Ron de Bruin's intro to macros:

(General, Regular and Standard modules all describe the same thing.)


Dave Peterson
Chris Bode replied on 15-Mar-09 02:24 AM
try following code


Private Sub mySub()
Dim row As Integer, col As Integer
row = 1
col = 1
Dim str As String
Dim i As Integer

While Sheet1.Cells(row, col).Value <> ""
'*F0123450 ---> *F*123450*
str = Sheet1.Cells(row, col).Value

If Mid(str, 3, 1) = "0" Then
str = Left(str, 2) & "*" & Mid(str, 4, Len(str))
Sheet1.Cells(row, col).Value = str
End If

row = row + 1
End Sub


Convert your Excel spreadsheet into an online calculator.

Chris Bode