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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry - 22 Feb 2008 19:55 GMT

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

Reply to this Message

Mike H - 22 Feb 2008 20:32 GMT

Jerry,

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

Mike

> 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

Reply to this Message

Gord Dibben - 23 Feb 2008 00:12 GMT

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

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 00:13 GMT

>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

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 00:31 GMT

>>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
[quoted text clipped - 27 lines]
>
>--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

Reply to this Message

Jerry - 23 Feb 2008 17:35 GMT

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

> 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

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 18:01 GMT

>Supplemental to initial post:
>
[quoted text clipped - 3 lines]
>
>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

Reply to this Message