LOWER and SUBSTITUTE all non-alpha characters in column with a hyp

[Jerry]

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

 

[Mike H]

Jerry,

I don't understand the question. Post an example before and after string

Mike

 

[Gord Dibben]

LOWER won't operate on non-alpha characters.

What do you really want to do?

Is your current data (in Run Jane, see Spot jump)? : )

What do you want it to look like when lowered and substituted?


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Jerry]

Supplemental to initial post:

Have a column that contains names, numbers, symbols and spaces. I need to
substitute all symbols and spaces with a hyphen, and alpha characters in
lower case.

Thank you, Jerry

 

[Ron Rosenfeld]

Supplemental to initial post:

Have a column that contains names, numbers, symbols and spaces. I need to
substitute all symbols and spaces with a hyphen, and alpha characters in
lower case.

Thank you, Jerry

If you consider a number to be an alpha character, then use the code below; if
not, remove 0-9 from re.pattern below.

To implement this, see my first response to you earlier in this thread.

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