On Mon, 06 Aug 2007 20:00:30 -0400, Ron Rosenfeld
wrote:

On Mon, 6 Aug 2007 10:32:17 -0700, pokdbz
wrote:

I put this into the cell formula and it worked fine.

What I need it for is a macro in VBA. I tried it in there and it gave the
error on Substitue and I replaced that with Replace but now Its giving me an
error on the Find .

Do you have any suggestions?

Here's a different approach:

=================================
Option Explicit
Sub foo()
Dim c As Range
Dim s() As String

For Each c In Selection
s = Split(c.Text, "_")
ReDim Preserve s(UBound(s) - 1)
c.Offset(0, 1).Value = Join(s, "_")
Next c

End Sub
=================================
--ron

Might want to do an error handler:

=========================
Option Explicit
Sub foo()
Dim c As Range
Dim s() As String

On Error Resume Next
For Each c In Selection
s = Split(c.Text, "_")
ReDim Preserve s(UBound(s) - 1)
c.Offset(0, 1).Value = Join(s, "_")
Next c

End Sub
==========================
--ron