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

  • Thread starter Thread starter Jerry
  • Start date Start date
J

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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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
Click to expand...


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
 
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
 
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
Click to expand...

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
 
Back
Top