How to format text and numbers as custom

[Guest]

How do you format a drivers license number? For example:
Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text?
Do you have to use a macro? If so, does anyone have the code for this
sequence?

 

[Don Guillett]

If always H then custom format of "H"&000-etc should do it
or format as text and type in the - - - -
or a worksheet change event to convert based on any entered letter and
numbers entered witout - - -

 

[Guest]

The "H" is just an example. In Florida, a drivers license # always starts
with the first letter of a persons last name, followed by twelve digits in a
sequence of 3,3,2,3,1...for example: John Doe is: "D545-360-49-586-0". I
want to be able to enter the letter and numbers in a single cell without
having to input the dashes to speed up data entry. The problem is that if I
use an @ symbol to allow text, then I can't use the # symbol (or so it
seems). A dash could come after the letter if that would help (D-545).

 

[David McRitchie]

Hi Julian,
The following macro will format based on the text value of cell
so it should work on cells that have text, numbers, formatted
numbers with leading zeros. It will make no difference that
the first character is a letter. If it say commas or decimal
points they would just be characters. the only thing this does
is insert hyphens based on your pattern.

Sub Format_with_hyphens(Optional formatSTR As String)
'-- The parameter here can only be used if invoked from another macro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, j As Long, result As String
Dim cell As Range, newStr As String, tstLen As Long
On Error Resume Next 'In case no cells in selection
If formatSTR = "" Then
newStr = "Hxxx-xxx-xx-xxx-x"
Else
newStr = formatSTR
End If
tstLen = Len(Replace(newStr, "-", ""))
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Len(cell.Text) = tstLen Then
j = 1
result = ""
For i = 1 To Len(newStr)
If Mid(newStr, i, 1) = "-" Then
result = result & "-"
Else
result = result & Mid(cell.Text, j, 1)
j = j + 1
End If
Next i
cell.Value = "'" & result
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

right click sheet tab>view code>insert this>finish to suit>SAVE
now anything typed in col A below row 1 will change automatically

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target = UCase(Left(Target, 4)) _
& "-" & Mid(Target, 4, 3) & "-" & Mid(Target, 8, 3) 'etc

Application.EnableEvents = True
End Sub