Custom Number Format including Text

Closed
Excel Newbie - Apr 5, 2012 at 06:40 AM
aquarelle Posts 7118 Registration date Saturday April 7, 2007 Status Moderator Last seen July 29, 2022 - Apr 5, 2012 at 01:07 PM
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

1 reply

aquarelle Posts 7118 Registration date Saturday April 7, 2007 Status Moderator Last seen July 29, 2022 491
Apr 5, 2012 at 01:07 PM
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
0