COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### How to remove all punctuation EXCEPT dashes?

• Follow

```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

```
 0

```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
>
>

```
 0

```How about this?

Left( Abs( TextToNum( Phone ) ) , 3 )  & "-" &
Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) &  "-" &
Right( Abs( TextToNum( Phone ) ) , 4 )

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

```
 0

```In reply to s5e%a.22612\$vo2.6865@newsread1.news.atl.earthlink.net on 8/15/03

Slightly Modified

Left( Abs( TextToNum( Text ) ) , 3 )  & "-" &
Middle( Abs( TextToNum( Text ) ) , 4 , 3 ) &  "-" &
Right( Abs( TextToNum( Text ) ) , 4 )

Lee

:)
>
> Left( Abs( TextToNum( Phone ) ) , 3 )  & "-" &
> Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) &  "-" &
> Right( Abs( TextToNum( Phone ) ) , 4 )
>
>
>
> <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
>>
>>
>
>

```
 0

```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 )

"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
>
>
8/15/03
> >
> > Slightly Modified
> >
> > Left( Abs( TextToNum( Text ) ) , 3 )  & "-" &
> > Middle( Abs( TextToNum( Text ) ) , 4 , 3 ) &  "-" &
> > Right( Abs( TextToNum( Text ) ) , 4 )
> >
> > Lee
> >
> > :)
> >>
> >> Left( Abs( TextToNum( Phone ) ) , 3 )  & "-" &
> >> Middle( Abs( TextToNum( Phone ) ) , 1 , 3 ) &  "-" &
> >> Right( Abs( TextToNum( Phone ) ) , 4 )
> >>
> >>
> >>
> >> <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
> >>>
> >>>
> >>
> >>
> >
>

```
 0

4 Replies
349 Views

Similiar Articles:

7/17/2012 6:01:27 AM