Custom Number Format including Text

[Closed]
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
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

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
487
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