Phone Number formatting

  • Thread starter Thread starter ChrisMattock
  • Start date Start date
C

ChrisMattock

Hi all, I have a cell that NEEDS to be of the following format:

0#### ######

I have set this up under formatting, custom... and when I type a number
like this...

01304999999 I get the result 01304 999999 PERFECT!

Unfortunately if I type this....

01304 999 999 I get this result 01304 999 999 which ain't good as it
mucks up my Macro, whih incidentally is this...

appWD.ActiveDocument.Bookmarks("PMTelephone").Range =
Format(strPMTelephone, "0#### ######")

Any ideas?
 
Chris,
Maybe a Worksheet event to format the cell; the following is a
"Starter" but maybe needs code to check length of input and check target
address if just one cell.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit
If Target.Column <> 1 Then GoTo wsexit '<==== not coulmn A
Application.EnableEvents = False
Target.Value = Format(Replace(Target.Value, " ", ""), "0#### ######")
wsexit:
Application.EnableEvents = True
End Sub
 
Hi Chris,

the STD code of a (UK) phone number is not always 5 characters - you
can have 4, 5 6 or 7, and phone numbers themselves can vary in length -
how would you want to display 118118, for example?

I find it better to treat phone numbers as text, so the leading zero(s)
are preserved and to just display them as is without any extra spaces.
I know this is not what you are asking, but ...

hope this helps.

Pete
 
Thanks Pete, that is an excellent point.... annoying.... but excellen
point. Lol. Text it is
 
Back
Top