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

"Julian Ganoudis" <Julian wrote in
message ...
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

"Julian Ganoudis" <Julian wrote in
message ...
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 wrote in message
...
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 tabview codeinsert thisfinish to suitSAVE
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

"Julian Ganoudis" <Julian wrote in
message ...
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

"Julian Ganoudis" <Julian wrote in
message ...
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?