Search and Replace Problem

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello,

I want to replace "2008-*" in a TEXT formated column

2008-09-22
2008-09-24
2008-09-26
2008-09-27

and get this result in return...

09-22
09-24
09-26
09-27

-=-

As easy as this looks.. I've never been able to figure it out as EXCEL
automatically REFORMATS my cell as a DATE all text values get lost and this
is the end result

22-Sep
24-Sep
26-Sep
27-Sep

with editable cell values changed to...

9/22/2008
9/24/2008
9/26/2008
9/27/2008

-=-

I have tried everything to keep the "TEXT" format during the replace, but
nothing seems to work. I need this a MARCO, so if you have an easy fix,
please make it in Excel Macro form.

Thanks :-)))

P.S. I know this one below is probably the easiest this in the world, but
how do I select the "current row" or "current column" (separately) without
specifying a column range. I just want "current" or "active" one.
 
are you using the find/replace dialog?
find 2008-
replace with
'

that's a single quote in the replace box.

if you have code, post the relevant part.
 
Try this macro...

Sub RemoveYear()
Dim R As Range
For Each R In Range("A1:A10")
If R.Value Like "####-##-##" Then
R.NumberFormat = "@"
R.Value = Mid(R.Value, 6)
End If
Next
End Sub
 
P.S. I know this one below is probably the easiest this in the world, but
how do I select the "current row" or "current column" (separately) without
specifying a column range. I just want "current" or "active" one.
Click to expand...

Sorry, I didn't see this question on my first reading of your post. To
select the row or column for the row or column the active cell is in, try
these...

ActiveCell.EntireRow.Select

ActiveCell.EntireColumn.Select
 
Back
Top