Hi pikapika13,
pikapika13 wrote:
I have a column formatted as text. I have data that looks like this:
"1 through 5" or "2 through 14". My macro does a find and replace:
Range("D2:D100").Replace _
What:=" through ", Replacement:="-"
My result is not what I want; it gives me dates. Desired outputs are
"1-5" or "2-14".
What do I need to do to accomplish this?
This is happening because Excel is reevaluating the entries as dates
(because they look like dates with the hyphens). The only thing I can think
of is to being each cell with a single quote (apostrophe) before running
your macro. That will force Excel to continue to treat the entries as text.
You could do it all with a loop like this:
Sub ReplaceThroughWithHyphen()
Dim c As Range
For Each c In Range("A2:D100")
If c.Value Like "* through *" And c.PrefixCharacter < "'" Then
c.Value = "'" & Replace(c.Value, " through ", "-")
End If
Next c
End Sub
--
Regards,
Jake Marx
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]