How do I convert nnnnnnnnnn to nnn nnn nnnn (Telephone numbers)?

[SouthAfricanStan]

Using Win XP, Excel 2003.
I have hundreds of telephone numbers (exported from Outlook) in a worksheet
column, some local, some international, all formatted as "General"
1) A formula or function to convert the following?:
nnn nnn nnnn from nnnnnnnnnn
also
nnn nnn nnn nnnn from nnnnnnnnnnnnn
also
nn nnn nnn nnnn from nnnnnnnnnnnn
etc, etc
In essence each telephone number is to be converted to a groups of numbers
(with a space between each group) with the last group contains 4 digits,
with preceding groups (upto four) containing 3 digits and the first group
either 1,2 or 3 digits.- maximum 19 digits.
It will not be required for Outlook to dial these numbers (after importing
into Outlook) - they will be printed out on a paper telephone index.
2) Will it be possible to do this with one formula or function, or will it
require several, depending on the number of digits in each number?
Thanks in advance.
Stan

 

[Dave Peterson]

Maybe you could use a custom format:
## ### ### 000 0000

Or if you really need the text:
=text(a1,"## ### ### 000 0000")

(although I don't know what etc, etc really means)

 

[MartinW]

Hi Stan,

You may need to sort your information into blocks to make applying
your custom formatting easier. If there is no readily available column
in your data to sort on, you could insert a helper column and put
=LEN(A1) in the first cell and drag down to the end of your data
and then sort on that column.
That's assuming your numbers are in column A.
You may also want to put a second helper column in numbered
1,2,3 etc down the column so that you can re-sort your data back to
it's original state, once you are finished

HTH
Martin

 

[Don Guillett]

Have a look in the help index for MID and LEFT and RIGHT
Or, use a macro with INSTREV

 

[Don Guillett]

try this
Sub fpn1() 'works to 13 digits
On Error Resume Next
For Each c In Range("b3:b7")
z = Right(c, 4)
y = Mid(c, Len(c) - 6, 3)
x = Mid(c, Len(c) - 9, 3)
w = Left(c, Len(c) - 10)
'MsgBox w & " " & x & " " & y & " " & z
c.Offset(, 1) = w & " " & x & " " & y & " " & z
Next c
End Sub

 

[SouthAfricanStan]

Thanks, but I found a very simple solution, no matter how many digits....:
Tools>Options>International>Number Handling, clear "Use system separators"
box.
Clear and hit the space bar in both "Decimal separator" and "Thousands
separator" boxes.
In the worksheet, format the relevant cells as numbers, with 4 decimals and
click positive in the "Use 1000 Separator"