How to format text and numbers as custom

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?

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 - - -

--
Don Guillett
SalesAid Software
[email protected]
"Julian Ganoudis" <Julian [email protected]> wrote in
message news:[email protected]...
> 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?

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).

"Don Guillett" wrote:

> 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 - - -
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Julian Ganoudis" <Julian [email protected]> wrote in
> message news:[email protected]...
> > 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?
>
>
>

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Julian Ganoudis" <Julian [email protected]> wrote in message
news:[email protected]...
> 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?

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


--
Don Guillett
SalesAid Software
[email protected]
"Julian Ganoudis" <Julian [email protected]> wrote in
message news:[email protected]...
> 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).
>
> "Don Guillett" wrote:
>
> > 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 - - -
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Julian Ganoudis" <Julian [email protected]> wrote in
> > message news:[email protected]...
> > > 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?
> >
> >
> >