How to format cells so they display A01-222-333-7777 or D01-222-3

[Guest]

I want to format a range of cells so that they display A01-222-333-7777 or
any other first letter in the string e.g. H02-111-444-8888 or
Z11-555-666-9999. I want to be able to just type the data and have Excel put
in the "-" between the entries, e.g. A012223337777 (Keyboard entry).

Text letter first, followed by a string of numbers that will be used as a
text only display with hypens between them. There will always be two numbers
following the the first letter, followed by three numbers with a dash and
three numbers with a dash ending with four numbers seperated with a dash.The
range will not be used in any calculations. Similar to a SSN but with a
letter designation first.

 

[Dave Peterson]

You can't use format|cells|Numberformat--that only works with numbers.

But you could use a worksheet event.

If you want to try...

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count > 1 Then Exit Sub 'one cell at a time!
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then Exit Sub

If Len(.Text) <> 13 Then
'do nothing
Else
Application.EnableEvents = False
.Value = Left(.Value, 3) & "-" _
& Mid(.Value, 4, 3) & "-" _
& Mid(.Value, 7, 3) & "-" _
& Right(.Value, 4)
Application.EnableEvents = True
End If
End With

End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[RagDyer]

If you have the room on your sheet, you could designate a column strictly
for data input, and then use text formulas to display your data where and
how you wish.

Say you use Column T for your data input.

Enter this wherever you wish to display your data:

=LEFT(T1,3)&"-"&MID(T1,4,3)&"-"&MID(T1,7,3)&"-"&RIGHT(T1,4)

You can copy this down as needed to display the other contents of Column T.