Search and Replace Problem

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.
 
G

Gary Keramidas

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.
 
R

Rick Rothstein

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
 
R

Rick Rothstein

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top