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.

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

--
Rick (MVP - Excel)


"John" wrote in message
...
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.
--


Gary


"John" wrote in message
...
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.

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

--
Rick (MVP - Excel)