I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?
Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212
I want them to end up all looking like (555) 555-1212 in my query results.
Thanks,
P. 4 1617
You could put these functions into a Standard Module:
Dim RegularExpressi on As Object
Private Function InitializeRegul arExpression()
Set RegularExpressi on = CreateObject("V BScript.RegExp" )
With RegularExpressi on
.Global = True
.IgnoreCase = True
End With
End Function
Public Function NumericOnly$(By Val vInput As String)
If RegularExpressi on Is Nothing Then InitializeRegul arExpression
With RegularExpressi on
.Pattern = "[^0-9]"
NumericOnly = .Replace(vInput , "")
End With
End Function
Public Function FormattedTeleph oneNumber$(ByVa l vInput)
FormattedTeleph oneNumber = Format(NumericO nly(vInput), "\(000\)\
000\-0000")
End Function
You could use them as in:
"SELECT FormattedTeleph oneNumber([Business Phone]) FROM Customers"
On May 17, 8:44*am, "Pat_RI" <pat2469...@zzc ox.netwrote:
I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?
Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212
I want them to end up all looking like (555) 555-1212 in my query results.
Thanks,
P.
Hi
I assume the slashes, parenthesis, dashes, etc are actually in the text of
the field and that its not so much the
format that you want to change but the but the content.
Look at the replace function in the Access VBA help function.
Create an update query and using the replace function Get rid of all of the
characters other than numerals.
Update to: Replace(TelNo,"-","") 'This gets rid of all the dashes in the
records that have them.
Do the same for the other characters.
You'll be left with 10 digit numeric strings.
If you want to include the actual characters in your data you can do another
update query to put them in.
You can then put in the characters that you want where you want them with
something like:
Update to: "(" & left(TelNo,3) & ") " & mid(TelNo)4,4) & "-" &
right(TelNo,4)
That would yield (781) 678-9807.
Regards
Kevin
"Pat_RI" <pa********@zzc ox.netwrote in message
news:xq******** *********@newsf e18.lga...
>I need to reformat a cell that contains telephone numbers to a format of (###) ###-#### but the original cells have various symbols such as / . - \ () etc is there a way to replace all of these symbols and then reformat accordingly?
Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212
I want them to end up all looking like (555) 555-1212 in my query results.
Thanks,
P.
On Sat, 17 May 2008 08:44:10 -0400, Pat_RI wrote:
I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?
Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212
I want them to end up all looking like (555) 555-1212 in my query results.
Thanks,
P.
Directly in a query (all on one line).
NewPhoneNumbers :Format(Replace (Replace(Replac e(Replace(Repla ce(Replace([OldPhoneNumbers],"/",""),".","")," \",""),"-",""),"(","")," )",""),"(@@@ )
@@@-@@@@")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Hi -
For A97 dinosaurs (no built-in Replace() function):
? format(fSaveNum er2("( 800 -555/1212"), "(@@@) @@@-@@@@")
(800) 555-1212
with fSaveNumer2() being:
Public Function fSaveNumer2(pSt r As String) As String
'purpose: Removes all non-numeric characters from a
' string.
'coded by: raskew
'calls: IsNumeric()
'Input: ? fSaveNumer2("( 800)--555/1212")
'Returns: 8005551212
Dim strHold As String
Dim strKeep As String
Dim intLen As Integer
Dim n As Integer
strHold = Trim(pStr)
intLen = Len(strHold)
For n = 1 To intLen
If IsNumeric(Mid(s trHold, n, 1)) Then
strKeep = strKeep & Mid(strHold, n, 1)
End If
Next
fSaveNumer2 = strKeep
End Function
fredg wrote:
>I need to reformat a cell that contains telephone numbers to a format of (###) ###-#### but the original cells have various symbols such as / . - \
[quoted text clipped - 12 lines]
>Thanks, P.
Directly in a query (all on one line). NewPhoneNumber s:Format(Replac e(Replace(Repla ce(Replace(Repl ace(Replace([OldPhoneNumbers],"/",""),".","")," \",""),"-",""),"(","")," )",""),"(@@@ ) @@@-@@@@")
--
Message posted via AccessMonster.c om http://www.accessmonster.com/Uwe/For...ccess/200805/1 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ashish |
last post by:
I wanted to make a Java application which would dial a telephone
number using the modem. When the person on the other end lifts up the
telephone (answers the phone), the application should start playing an
audio file(eg. .wav file).
The approach I took was using the Java Communications
API(javax.comm.). As my modem is connected to the serial port on my
computer I send AT commands to make the phone call (ATDT2222222). This
is working...
|
by: John Bailo |
last post by:
The war of the OSes was won a long time ago.
Unix has always been, and will continue to be, the Server OS in the form
of Linux.
Microsoft struggled mightily to win that battle -- creating a poor man's
DBMS, a broken email server and various other /application/ servers to
try and crack the Internet and IS markets.
In the case where they didn't spend their own money to get companies to
|
by: Jouke Langhout |
last post by:
Hello all!
For quite some time now, I've got the following problem:
Access won't close properly when a user closes the application. An
ACCESS process stays active and that process can only be terminated by
pressing ++ and then terminate the process.
I searched the entire internet and found out that there could be two
things wrong (both of them are mentioned in the bug list on the access
|
by: Iftikhar Hussain |
last post by:
Hello Everyone
Nice to be back here, well all u guys have helped in past & now I would
like a favor again.
first what i want to develop is as follows:
An application for rescue station, where there is a Telephone & a
Computer System . the telephone is also connected with the computer
system, so when the telphone rings, the Computer can act as a CLI &
could identify, which number the call is coming from. it gets that
number, matches it...
|
by: Robert Johnson |
last post by:
Hi all. I need to format my text box's and combo box's with Telephone (###)
###-#### and Postal Code #####-#### or other custom formating, how do I do
this? I tried to go into Properties, Databindings, Advanced and to use the
Custom but it didn't work to well. To clarify: it placed (###) ###-#### in
my Telephone field but didn't guide my user or format the number after
entering data, so it was pretty useless. Obviously I didn't do it...
| |
by: Nkhosinathie |
last post by:
This isn't a homework,i want to improve my skills in programming and apply my understanding to pinters. i'm developing a program that will print telephone numbers randomly for a city for instance(maybe use a code for (011) and those telephone numbers should be equal to 10 numbers. so may someone please give a clue how to start this beautiful thinking of mine.thanks
|
by: Smythe32 |
last post by:
Hi,
I am in need of some assistance. I have a table with the following:
Tel_St_Range Tel_End_Range Customer Name
6075551200 6075551300 Tom Smith
I would like to know how to break out the ranges into a new table that
looks like this:
Telephone Customer Name
6075551200 Tom Smith
|
by: tlontz |
last post by:
I am trying to retrieve the telephone_number from active directory using the following script:
Dim search As New DirectorySearcher("")
search.Filter = "(&(objectCategory=Person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(samaccountname=" + UserName.ToString.Trim + "))"
'search.Filter = "(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))( objectCategory=person)(samaccountname=" +...
|
by: rikigr4 |
last post by:
Hi All,
I am looking for code written in .NET that provides connection between application and telephone.
So that - the telephone will return a code according to input code that it get's from the user.
I'll be happy to get even a little help - if you know something that relate and can help me!!
Thanks. http://www.codeproject.com/script/Forums/Images/smiley_wink.gif http://www.codeproject.com/script/Forums/Images/smiley_confused.gif
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |