Insert Dashes In Alpha/Numeric Sequence

[data808]

I wanted to format the cell to do this but looked online and it looks like this is not possible because of the alpha characters in the string with numbers.

In column C I will be entering a specific number that has letters and numbers. Here is the format:

AZ-1234-1-123456-1234

If there is a way to do it by just formatting the cell then please let me know. If not, what is the next best way to do it? VBA or Formula? Not sure how to do it so if you could send suggestions then that would be great.

Thank you.

 

[Scott Huish]

=LEFT(A1,2)&TEXT(RIGHT(A1,15),"-0000-0-000000-0000")

 

[SunnyAlv]

AZ-1234-1-123456-1234

this is input, how bout the ouput u want ?

 

[Scott Huish]

this is input, how bout the ouput u want ?

I think that's the output. I think the OP wants to enter that without the hyphens and have them added in.

 

[data808]

this is input, how bout the ouput u want ?

Sorry yes, Scott is correct. I would like to enter the value without dashes and have it added in.

 

[data808]

=LEFT(A1,2)&TEXT(RIGHT(A1,15),"-0000-0-000000-0000")

Sorry this did not work for me.

I am trying to type into column C cells except for rows 1 and 2 because those rows have my title headers. So basically I will type into cell C3, C4, C5, etc... and keep going down forever in that column. The values I will be typing into column C will be like this:

AZ123411234561234

but I want it to look like this when I tab over:

AZ-1234-1-123456-1234

Does that make sense?

 

[Scott Huish]

You do need to change the cell reference to where ever your data is. How is this not what you want?

Book2
	C	D
3	AZ123411234561234	AZ-1234-1-123456-1234
Sheet1

Cell Formulas
Range	Formula
D3	D3	=LEFT(C3,2)&TEXT(RIGHT(C3,15),"-0000-0-000000-0000")

 

[Scott Huish]

If you want it to happen in place, you'll need VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If Len(c) = 17 Then c = Left(c, 2) & Format(Right(c, 15), "-0000-0-000000-0000")
Next
Application.EnableEvents = True
End Sub

 

[data808]

If you want it to happen in place, you'll need VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If Len(c) = 17 Then c = Left(c, 2) & Format(Right(c, 15), "-0000-0-000000-0000")
Next
Application.EnableEvents = True
End Sub

Yes thank you. This is what I was looking for. I haven't tested it yet but I will soon and come back to give a status update. Thank you so much for the help.

 

[data808]

If you want it to happen in place, you'll need VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If Len(c) = 17 Then c = Left(c, 2) & Format(Right(c, 15), "-0000-0-000000-0000")
Next
Application.EnableEvents = True
End Sub

Working wonderfully. Thank you very much!