On Fri, 22 Feb 2008 19:13:38 -0500, Ron Rosenfeld
wrote:

On Fri, 22 Feb 2008 11:55:00 -0800, Jerry
wrote:

Occasional Excel 2000 user, can anyone please show me how to LOWER and
SUBSTITUTE all non-alpha characters in column with a hyphen (in Run Jane, see
Spot jump)? : )

Thanks, Jerry

You can do it with a UDF.

<alt-F11 opens the VB Editor. Ensure your project is highlighted in the
project explorer window, then Insert/Module and paste the code below into the
window that opens.

To use this, merely enter the formula

=NonAlphaDash(cell_ref)

into some cell.

================================
Option Explicit
Function NonAlphaDash(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "[^A-Z]"
NonAlphaDash = re.Replace(str, "-")
End Function
===========================

--ron


Of course, what this does is substitute a hyphen for all non-Alpha characters.

If you want to output all in lowercase, merely make these slight changes:

=====================
Option Explicit
Function NonAlphaDash(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^a-z]"
NonAlphaDash = re.Replace(LCase(str), "-")
End Function
===============================
--ron