Custom Number Format including TextClosed

Question

Hello,

I'm really sorry if this is a 'silly' question, but I'd really appreciate any help that any of you great volunteers may be able to give me.

I am downloading data which appears in the format P123456789123 but I would like to format it to appear as P1234-56789-123. 
I've tried #####-#####-### and @####-#####-### with no luck, I think because there's a letter at the beginning rather than a number.

Is anyone able to help me tweek this so that I can read it easier? 

Also, is there any way to copy the middle section of the code to the next column? I would normally use something like right(A1,5), but that obviously won't work here. 

Many Thanks for your help in advance. 

An Excel Newbie 

Configuration: Windows Vista / Internet Explorer 7.0

aquarelle · Answer

Hi,

You can use this formula into an other column (adapt it for your data) :
=LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&RIGHT(A1,3)

or you can use a vba macro, if you know how to use it like this :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cible As Range, Cible_2 As String

If Not Intersect(Target, Range(Range("A1"), Range("A1").End(xlDown))) Is Nothing Then
Set Cible = Target
If Len(Cible) = 0 Then Exit Sub
    If Len(Cible) <> 13 Then
        Cible_2 = Application.WorksheetFunction.Substitute(Cible, "-", "")
        If Len(Cible_2) = 13 Then Exit Sub
        Cible.ClearContents
        Cible.Activate
        MsgBox "You should type 13 characters."
    Else
        Target = Left(Cible, 5) & "-" & Mid(Cible, 6,5) & "-" & Right(Cible, 3)
    End If
End If
End Sub


Best regard

Custom Number Format including Text

1 response

Similar discussions

Subscribe To Our Newsletter!