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

[Bridget Eley]

The trick is to take everything but the numbers out using nested Substitute
functions (I'll call this CalcA:

Substitute(Substitute(Substitute(Substitute(Substi tute(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(S ubstitute(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.co m,
[email]mojotogo@sbcglobal.net[/email] at [email]mojotogo@sbcglobal.net[/email] 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
>
>

[Shadenfroh]

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.prodi gy.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
>
>

[Lee Smith]

In reply to s5e%a.22612$vo2.6865@newsread1.news.atl.earthlink. net on 8/15/03
4:33 PM by Shadenfroh [email]shadenfroh@yahoo.com[/email]:

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.prodi gy.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
>>
>>

>
>

[Shadenfroh]

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
> [email]lee@pacific.net[/email]:
>
> 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 [email]shadenfroh@yahoo.com[/email]:
> >
> > 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.prodi gy.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
> >>>
> >>>
> >>
> >>

> >

>