Custom number format for driver's license number

[excel user]

I need to create a custom number format for a wisconsin driver's license
number (i.e. "S123-4567-8912-34") How can this be done?

 

[RagDyeR]

Assuming quotes ( " " ) not needed:

\S###-####-####-##
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I need to create a custom number format for a wisconsin driver's license
number (i.e. "S123-4567-8912-34") How can this be done?

 

[excel user]

I tried it, with no success. Also, The "S" is not static, I need it to
display whatever letter I type.

 

[Ron Rosenfeld]

On Wed, 16 Jul 2008 08:58:05 -0700, excel user <excel

I need to create a custom number format for a wisconsin driver's license
number (i.e. "S123-4567-8912-34") How can this be done?

000-0000-000-00 will do for the numeric part of the number,

\S000-0000-000-00 will do for the above where the numeric part is preceded by
the letter "S". (You would just enter the numbers)

but I don't think you can have a variable preceding letter in the format.

If you put your letter in A1, and the number in B1, then perhaps:


C1: =TEXT(B1,"\"&UPPER(A1)&"000-0000-000-00")

--ron

 

[excel user]

That's unfortunate. You'd think MS would have the ability to store driver's
license numbers in their correct format. They should have the formats
pre-defined just as they do with "Social Securtiy Number" and "Zip Code".
It's pretty rare I find something that MS Excel cannot do. What about using
an event driven macro - would that work? How would it work? Would you be
able to use the same cell?

 

[John C]

Building off Ron's formula, since the cell of entry has the letter, as well
as HAS to have all the digits, even zerios, the following would work:
C1: =UPPER(LEFT(B1,1))&TEXT(RIGHT(B1,LEN(B1)-1),"000-0000-0000-00")

Assuming B1 is where the 'raw' data is entered.

 

[Dave Peterson]

You could use a worksheet_Change event.

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
Dim myStr As String

Set myRngToCheck = Me.Range("a:a")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myIntersect.Cells
myStr = ""
If UCase(myCell.Value) Like "[A-Z]#############" Then
'do the conversion
myStr = UCase(Left(myCell.Value, 1)) _
& Format(Mid(myCell.Value, 2), "000-0000-0000-00")
myCell.Value = myStr
End If
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub

I checked all of column A. Change this line to match your input range:

Set myRngToCheck = Me.Range("a:a")

 

[John C]

With some error checking:
=IF(B1="","",IF(AND(LEN(B1)=14,OR(AND(CODE(LEFT(B1,1))>64,CODE(LEFT(B1,1))<91),AND(CODE(LEFT(B1,1))>96,CODE(LEFT(B1,1))<123)),ISNUMBER(--RIGHT(B1,LEN(B1)-1))),UPPER(LEFT(B1,1))&TEXT(RIGHT(B1,LEN(B1)-1),"000-0000-0000-00"),"Invalid
Entry"))

 

[Chip Pearson]

That's unfortunate. You'd think MS would have the ability to store

driver's
license numbers in their correct format. They should have the formats
pre-defined just as they do with "Social Securtiy Number" and "Zip Code".

Every state has its own license numbering scheme and number format. While
there would be some duplication among the states, you'd still be looking at
a few dozen different formats.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

And don't forget other countries Chip.

Could add a few more formats<g>


Gord

 

[excel user]

Excellent. This code worked perfectly! Thank you very much for your help!

You could use a worksheet_Change event.

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
Dim myStr As String

Set myRngToCheck = Me.Range("a:a")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myIntersect.Cells
myStr = ""
If UCase(myCell.Value) Like "[A-Z]#############" Then
'do the conversion
myStr = UCase(Left(myCell.Value, 1)) _
& Format(Mid(myCell.Value, 2), "000-0000-0000-00")
myCell.Value = myStr
End If
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub

I checked all of column A. Change this line to match your input range:

Set myRngToCheck = Me.Range("a:a")

That's unfortunate. You'd think MS would have the ability to store driver's
license numbers in their correct format. They should have the formats
pre-defined just as they do with "Social Securtiy Number" and "Zip Code".
It's pretty rare I find something that MS Excel cannot do. What about using
an event driven macro - would that work? How would it work? Would you be
able to use the same cell?