I said "Keep this text! Not a date!"

[pikapika13]

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?

[Jake Marx]

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]