By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,341 IT Pros & Developers. It's quick & easy.

Remove unwanted charcters from phone numbers

P: n/a
Hi
I need to remove unwanted characters from phone numbers from my phone bill
to analyse the data.
Some examples are
02 48222222 i need to remove the space
+61266667656 i need to remove the +
0427 221 529 i need to remove both spaces
0419 637 344MNET i need to remove the spaces and the MNET
telstra.wap i need to remove all of this
+61408400510MNE i need to remove the + and MNE

I think Telstra my phone company has done this on purpose to stop people
like myself from analysing their phone calls

I have tried using right, left and length functions in a query but this does
not seem to work properly and is cumbersome

Any help would be greatly appreciated

Thanks Danny
Jun 5 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Danny Yeadon wrote in message <44********@dnews.tpgi.com.au> :
Hi
I need to remove unwanted characters from phone numbers from my phone
bill to analyse the data.
Some examples are
02 48222222 i need to remove the space
+61266667656 i need to remove the +
0427 221 529 i need to remove both spaces
0419 637 344MNET i need to remove the spaces and the MNET
telstra.wap i need to remove all of this
+61408400510MNE i need to remove the + and MNE

I think Telstra my phone company has done this on purpose to stop
people like myself from analysing their phone calls

I have tried using right, left and length functions in a query but
this does not seem to work properly and is cumbersome

Any help would be greatly appreciated

Thanks Danny


I think this calls for a user defined function, where I'd probably loop
the string, verify each character and append the numbers to the result
string - perhaps something like the air code below (try it in a
standard module, not a form/report class module)

public function stripunvanted(byval v_strIn) as string
dim lngCount as long
if not isnull(v_strIn) then
for lngCount = 1 to len(v_strIn)
if isnumeric(mid$(v_strIn, lngCount, 1)) then
strOut = strOut & mid$(v_strIn, lngCount, 1)
end if
next lngCount
end if
stripunvanted = strOut
end function

call it with stripunvanted([name of your field]) in the query.

--
Roy-Vidar
Jun 5 '06 #2

P: n/a
dim s as string

s = replace(FormattedPhoneNumber, "+", "")
s = replace(s, " ", "")
FormattedPhoneNumber would be replaced by whatever your field name is that
holds the formatted phone number.

Be sure to notice that after the first Replace you reference s, not the
original field.
Jun 5 '06 #3

P: n/a
Thanks Roy
It worked Beautifully

Danny

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Danny Yeadon wrote in message <44********@dnews.tpgi.com.au> :
Hi
I need to remove unwanted characters from phone numbers from my phone
bill to analyse the data.
Some examples are
02 48222222 i need to remove the space
+61266667656 i need to remove the +
0427 221 529 i need to remove both spaces
0419 637 344MNET i need to remove the spaces and the MNET
telstra.wap i need to remove all of this
+61408400510MNE i need to remove the + and MNE

I think Telstra my phone company has done this on purpose to stop people
like myself from analysing their phone calls

I have tried using right, left and length functions in a query but this
does not seem to work properly and is cumbersome

Any help would be greatly appreciated

Thanks Danny


I think this calls for a user defined function, where I'd probably loop
the string, verify each character and append the numbers to the result
string - perhaps something like the air code below (try it in a
standard module, not a form/report class module)

public function stripunvanted(byval v_strIn) as string
dim lngCount as long
if not isnull(v_strIn) then
for lngCount = 1 to len(v_strIn)
if isnumeric(mid$(v_strIn, lngCount, 1)) then
strOut = strOut & mid$(v_strIn, lngCount, 1)
end if
next lngCount
end if
stripunvanted = strOut
end function

call it with stripunvanted([name of your field]) in the query.

--
Roy-Vidar

Jun 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.