4 leading digit, Custom number format

[hamncheese]

Hi, does anyone know how to Custom Format number to "force" excel to start 4 digit from the beginning of number ?
This is the list of number that i have :

and i need to change them into this format :

Thank you for anyone willing to read and help me with this

Regards, Erik

 

[hrayani]

Hi Erik

For 11 digits you can set the format like this > "+"####"-"####"-"###
For 12 digits you can set the format like this > "+"####"-"####"-"####
For 13 digits you can set the format like this > "+"####"-"####"-"####"-"#

 

[hamncheese]

hi, thank you for the help
i apologize for not being clearer with my question
i need a Custom Number Format Rule for 1 whole Column that contains several different digits
such in my example, in 1 column i have :
- 11 digits
- 13 digits
(there are other digits also such as : 12 and 14)

if im not mistaken, from what you propose 1 column can only have 1 type of digits

as for now i have to settle with this solution :

 

[hrayani]

Hi Erik,

This is all I have... but I am sure that experts out here will come up with a better option

Regards,

Humayun

 

[yky]

I doubt one number format rule can suit all different digits but you can write a VBA script to go through each cell, check the length of digits, and apply the proper number format assuming one digit length has only one format. Something like the following:

Sub num_format()

Dim rng As Range
Dim cell As Range
Dim cell_length As Integer

Set rng = Range("A1:A4")

For Each cell In rng
cell_length = Len(cell)

Select Case cell_length

Case 11
cell.NumberFormat = "+####-####-###"

Case 12
cell.NumberFormat = "+####-####-####"
Case 13
cell.NumberFormat = "+####-####-####-#"
End Select

Next

End Sub

 

[lrobbo314]

Could use a custom function as well.

Book1
	A	B
15	62224230473	+6222-4230-473
16	6283822595688	+6283-8225-9568-8
17	62856200778	+6285-6200-778
18	628112237260	+6281-1223-7260
Sheet6

Cell Formulas
Range	Formula
B15:B18	B15	=rx(A15)

Function RX(s As String)
With CreateObject("VBSCript.RegExp")
    .Pattern = "((?:\d{4}))"
    .Global = True
    If .test(s) Then
        RX = "+" & .Replace(s, "$1-")
        If Right(RX, 1) = "-" Then RX = Left(RX, Len(RX) - 1)
    Else
        RX = "NA"
    End If
End With
End Function

 

[Rick Rothstein]

Hi, does anyone know how to Custom Format number to "force" excel to start 4 digit from the beginning of number ?
This is the list of number that i have :

I think we need to clarify... your cells are formatted as Text, correct? If they are formatted as Text, then your "numbers" are not really numbers and, hence, you cannot format the cells to display them differently. If they are not formatted as Text, then what are they formatted as and how are you getting so many digits into the cell without having the ending digits change to zeros?