On Fri, 13 Jun 2008 19:14:02 -0500, Minitman
wrote:

I had left out the Case 24 (the formatting that Peter T came up with
using Rick's "voodoo" formatting trick back in Jul 10, 2007) since I
thought it would be a less cluttered post and that it should be a
simple matter to reintegrate it into the final code, silly me.

But I don't understand vbscript or what is actually happening! I'm a
little afraid to start modifying code I don't understand!

Are there any special tricks that I should be aware of when attempting
to utilize and or modify your suggestions?

1. Rick's routine returns your result as a text string. Mine and Doug's return
a number formatted as a telephone number or extension. They would both appear
the same in the cell -- but Text and Numbers will behave differently in
formulas.

2. You would have to add the Column 24 to my list of both an acceptable Target
and also for a different format. Could you give an example of what it would
look like? And does the data in Column 24 also require removal of all
non-digits?

If so, mine is easily modified to something like:


================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTel As Range, rExt As Range, c As Range
Dim col As Object
Dim rMapsCo As Range
Dim re As Object

Set rTel = Union(Columns(19), Columns(21), Columns(37), Columns(39), _
Columns(41), Columns(43), Columns(45), Columns(47), _
Columns(49), Columns(51), Columns(53), Columns(55))

Set rExt = Union(Columns(20), Columns(22), Columns(38), Columns(42), _
Columns(44), Columns(46), Columns(48), Columns(50), _
Columns(52), Columns(54), Columns(56))

Set rMapsCo = Columns(24)

If Not Intersect(Target, Union(rTel, rExt, rMapsCo)) Is Nothing Then
Application.EnableEvents = False
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
For Each c In Target
If Not Intersect(c, rTel) Is Nothing _
And c.Row < 1 Then
c.Value = re.Replace(c.Value, "")
c.NumberFormat = "[<=9999999]###-####;(###) ###-####"
End If
If Not Intersect(c, rExt) Is Nothing _
And c.Row < 1 Then
c.Value = re.Replace(c.Value, "")
c.NumberFormat = """Ext. ""0"
End If
If Not Intersect(c, rMapsCo) Is Nothing _
And c.Row < 1 Then
c.Value = re.Replace(c.Value, "")
c.NumberFormat = """!Map ""0000 ""\<""00-00""\"""
End If
Next c
Application.EnableEvents = True
End If
End Sub
=================================

IF you prefer a text string output, then you can change the lines that output
the values, as in below:

============================
....
For Each c In Target
If Not Intersect(c, rTel) Is Nothing _
And c.Row < 1 Then
c.Value = Application.WorksheetFunction.Text _
(re.Replace(c.Value, ""), "[<=9999999]###-####;(###) ###-####")
End If
If Not Intersect(c, rExt) Is Nothing _
And c.Row < 1 Then
c.Value = Application.WorksheetFunction.Text _
(re.Replace(c.Value, ""), """Ext. ""0")
End If
If Not Intersect(c, rMapsCo) Is Nothing _
And c.Row < 1 Then
c.Value = Application.WorksheetFunction.Text _
(re.Replace(c.Value, ""), """!Map ""0000 ""\<""00-00""\""")
End If
Next c
....
==========================================

Also, for each segment (telephone, extension, MapsCo) you could test each
result for proper data, depending on the requirements, as I mentioned before.

If you have questions about the various code segments, feel free to ask.

In particular the Regular Expression pattern "\D+" refers to any characters in
the string that are not digits (i.e. not in the set [0-9]). the Replace
methods replaces all matches (all non-digits) with a null string.

The rest is pretty straightforward.
--ron