Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
Jerry,
I don't understand the question. Post an example before and after string Mike "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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
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 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
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 "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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LOWER and SUBSTITUTE all non-alpha characters in column with a hyp
On Sat, 23 Feb 2008 09:35:00 -0800, Jerry
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find alpha all characters | Excel Discussion (Misc queries) | |||
change column heading names from numbers to alpha characters | Excel Discussion (Misc queries) | |||
change Excel column headings from numbers to alpha characters | Excel Worksheet Functions | |||
Alpha characters problem? | Excel Worksheet Functions | |||
Check for Alpha characters | Excel Discussion (Misc queries) |