How to remove all punctuation EXCEPT dashes?

I need to standardize phone numbers that are entered many different ways. I can remove all punctuation then use left/middle/right functions to break apart the number and make a calculated version but is there an easier way? Can I remove parentheses, spaces, periods, and slashes from a phone number field without also removing the dashes? (216)555-1212 into 216-555-1212 (216) 555-1212 into 216-555-1212 (216) 555-1212 into 216-555-1212 (216)-555-1212 into 216-555-1212 (216)-555-1212 into 216-555-1212 216.555.1212 into 216-555-1212 216/555-1212 into 216-555-1212 and not 2165551212 Any help would be GREATLY appreciated TIA

The trick is to take everything but the numbers out using nested Substitute functions (I'll call this CalcA: Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Number, "(", ""), ")", ""), ".", ""), "-", ""), " ", ""), "/", "") Then put the dashes back in: Left(CalcA, 3) &"-" & Middle(CalcA, 4, 3) & "-" & Right(CalcA, 4) Final calc: Left(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Numbe r, "(", ""), ")", ""), ".", ""), "-", ""), " ", ""), "/", ""), 3) &"-" & Middle(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Num ber, "(", ""), ")", ""), ".", ""), "-", ""), " ", ""), "/", ""), 4, 3) & "-" & Right(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Numb er, "(", ""), ")", ""), ".", ""), "-", ""), " ", ""), "/", ""), 4) Bridget Eley in article ZDc%a.25617$Vx2.11488935@newssvr28.news.prodigy.com, mojotogo@sbcglobal.net at mojotogo@sbcglobal.net wrote on 16/8/03 7:54 AM: > I need to standardize phone numbers that are entered many different ways. I > can remove all punctuation then use left/middle/right functions to break > apart the number and make a calculated version but is there an easier way? > > Can I remove parentheses, spaces, periods, and slashes from a phone number > field without also removing the dashes? > > > (216)555-1212 into 216-555-1212 > (216) 555-1212 into 216-555-1212 > (216) 555-1212 into 216-555-1212 > (216)-555-1212 into 216-555-1212 > (216)-555-1212 into 216-555-1212 > 216.555.1212 into 216-555-1212 > 216/555-1212 into 216-555-1212 > > and not > > 2165551212 > > Any help would be GREATLY appreciated > > TIA > >

How about this? Left( Abs( TextToNum( Phone ) ) , 3 ) & "-" & Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) & "-" & Right( Abs( TextToNum( Phone ) ) , 4 ) Shadenfroh <mojotogo@sbcglobal.net> wrote in message news:ZDc%a.25617$Vx2.11488935@newssvr28.news.prodigy.com... > I need to standardize phone numbers that are entered many different ways. I > can remove all punctuation then use left/middle/right functions to break > apart the number and make a calculated version but is there an easier way? > > Can I remove parentheses, spaces, periods, and slashes from a phone number > field without also removing the dashes? > > > (216)555-1212 into 216-555-1212 > (216) 555-1212 into 216-555-1212 > (216) 555-1212 into 216-555-1212 > (216)-555-1212 into 216-555-1212 > (216)-555-1212 into 216-555-1212 > 216.555.1212 into 216-555-1212 > 216/555-1212 into 216-555-1212 > > and not > > 2165551212 > > Any help would be GREATLY appreciated > > TIA > >

In reply to s5e%a.22612$vo2.6865@newsread1.news.atl.earthlink.net on 8/15/03 4:33 PM by Shadenfroh shadenfroh@yahoo.com: Slightly Modified Left( Abs( TextToNum( Text ) ) , 3 ) & "-" & Middle( Abs( TextToNum( Text ) ) , 4 , 3 ) & "-" & Right( Abs( TextToNum( Text ) ) , 4 ) Lee :) > How about this? > > Left( Abs( TextToNum( Phone ) ) , 3 ) & "-" & > Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) & "-" & > Right( Abs( TextToNum( Phone ) ) , 4 ) > > Shadenfroh > > > <mojotogo@sbcglobal.net> wrote in message > news:ZDc%a.25617$Vx2.11488935@newssvr28.news.prodigy.com... >> I need to standardize phone numbers that are entered many different ways. > I >> can remove all punctuation then use left/middle/right functions to break >> apart the number and make a calculated version but is there an easier way? >> >> Can I remove parentheses, spaces, periods, and slashes from a phone number >> field without also removing the dashes? >> >> >> (216)555-1212 into 216-555-1212 >> (216) 555-1212 into 216-555-1212 >> (216) 555-1212 into 216-555-1212 >> (216)-555-1212 into 216-555-1212 >> (216)-555-1212 into 216-555-1212 >> 216.555.1212 into 216-555-1212 >> 216/555-1212 into 216-555-1212 >> >> and not >> >> 2165551212 >> >> Any help would be GREATLY appreciated >> >> TIA >> >> > >

Oops. Didn't notice that because of the previous error you pointed out. Try this (don't have time to test it myself at the moment). Left( Substitute ( Abs( TextToNum( Text ) ) , "." , "" ) , 3 ) & "-" & Middle( Substitute ( Abs( TextToNum( Text ) ) , "." , "" ), 4 , 3 ) & "-" & Right( Substitute ( Abs( TextToNum( Text ) ) , "." , "" ) , 4 ) Shadenfroh "Lee Smith" <lee@pacific.net> wrote in message news:BB62D104.2CD57%lee@pacific.net... > In reply to BB62C764.2CD50%lee@pacific.net on 8/15/03 5:22 PM by Lee Smith > lee@pacific.net: > > I just noticed that this calculation breaks on the one that has periods. > > 216.555.1212 > > ends up > > 216-.55-1212 > > Lee > > > > In reply to s5e%a.22612$vo2.6865@newsread1.news.atl.earthlink.net on 8/15/03 > > 4:33 PM by Shadenfroh shadenfroh@yahoo.com: > > > > Slightly Modified > > > > Left( Abs( TextToNum( Text ) ) , 3 ) & "-" & > > Middle( Abs( TextToNum( Text ) ) , 4 , 3 ) & "-" & > > Right( Abs( TextToNum( Text ) ) , 4 ) > > > > Lee > > > > :) > >> How about this? > >> > >> Left( Abs( TextToNum( Phone ) ) , 3 ) & "-" & > >> Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) & "-" & > >> Right( Abs( TextToNum( Phone ) ) , 4 ) > >> > >> Shadenfroh > >> > >> > >> <mojotogo@sbcglobal.net> wrote in message > >> news:ZDc%a.25617$Vx2.11488935@newssvr28.news.prodigy.com... > >>> I need to standardize phone numbers that are entered many different ways. > >> I > >>> can remove all punctuation then use left/middle/right functions to break > >>> apart the number and make a calculated version but is there an easier way? > >>> > >>> Can I remove parentheses, spaces, periods, and slashes from a phone number > >>> field without also removing the dashes? > >>> > >>> > >>> (216)555-1212 into 216-555-1212 > >>> (216) 555-1212 into 216-555-1212 > >>> (216) 555-1212 into 216-555-1212 > >>> (216)-555-1212 into 216-555-1212 > >>> (216)-555-1212 into 216-555-1212 > >>> 216.555.1212 into 216-555-1212 > >>> 216/555-1212 into 216-555-1212 > >>> > >>> and not > >>> > >>> 2165551212 > >>> > >>> Any help would be GREATLY appreciated > >>> > >>> TIA > >>> > >>> > >> > >> > > >

Similiar Articles:

How to remove all subdirectory/file under current directory ...
zsh issues a "zsh: sure you want to delete all the files ... rf ./*(D) (The (D) is to include the dot files except "." ... with how to create a file that starts with a dash ...

How to remove characters from a string - comp.soft-sys.matlab ...
> > string =3D { 'the table' } > > remove 'the ... numbers (7 & 10 digit) and many (but not all) of them have an extra dash ... is similar to the String.TrimEnd method except ...

How to keep only 5 most recent files in certain folder - comp.unix ...
... make a script that would run every couple hours and purge all files in certain folder except ... rm -- Fletcher Glenn email f-g-l-e-n-n@quest.com (remove the dashes)

Pseudorandom Noise Generator.... - comp.lang.vhdl
And there seems to be something wrong with your keyboard: It generates some extra punctuation at (pseudo?) random intervals. -- Stef (remove caps, dashes and ...

unicode display of common characters - comp.emacs
Apparently, when presented with a Windows em-dash ... I don't care for tidy except perhaps as a syntax error ... sed queation - remove all characters after a hyphen - comp ...

Web-Safe Fonts? - comp.fonts
Font lists for all operating ... www.daszeichen.ch remove nixspam to ... things as simple as correct punctuation characters (you know, curly quotes and real dashes ...

Could anyone give me the spice-mode.el - comp.emacs
Hi, All I am new to *NIX and I am thinking of writing spice code under Emacs. However, I have no idea of Emacs Lisp. Hence, I could not write a packa...

Sampling: What Nyquist Didn't Say, and What to Do About It - comp ...
Maybe it's the em-dashes. Jeez... I ... Something I try to avoid, except online ... help you move through the "text" -- all that indentation and decorative punctuation.

Initial Capitals - comp.lang.rexx
... any macx has the "x" left unchanged, | | except ... */ punct=".,'-" /*possible name punctuation.*/ /*remove punct and blanks.

Characters permitted in file names - comp.unix.programmer ...
-- To reply by e-mail, remove the "--nospam--" in the address ... is in force would be allowed to use anything except ... trouble. - comp.lang ... #3 ... the filename has a dash ...

How to remove all punctuation EXCEPT dashes? - FileMaker
I need to standardize phone numbers that are entered many different ways. I can remove all punctuation then use left/middle/right functions to break apart the number ...

PHP remove all non-alphanumerical characters except dashes
I have this to replace spaces with dashes which works just fine PHP Code: $stripped ... Code: [^A-Za-z0-9\-\._] ... thanks Simplistik the reg exp removes the dashes ...

7/17/2012 6:01:27 AM