I have a column formatted as text. I have data that looks like this: "
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 ar
"1-5" or "2-14".

What do I need to do to accomplish this

--
pikapika1
-----------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...fo&userid=1089
View this thread: http://www.excelforum.com/showthread.php?threadid=55617

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]