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.

*F0123450

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
dguillett1@austin.rr.com
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, _
Selection.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in the range"
Else
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:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(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

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
Wend
End Sub

--------------------



Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode