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?

Assuming quotes ( " " ) not needed:

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

HTH,

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


"excel user" <excel wrote in message
...
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?

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

"RagDyeR" wrote:

Assuming quotes ( " " ) not needed:

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

HTH,

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


"excel user" <excel wrote in message
...
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?

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

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

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?

"Ron Rosenfeld" wrote:

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

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

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.

--
John C


"Ron Rosenfeld" wrote:

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

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

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




excel user wrote:

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?

"Ron Rosenfeld" wrote:

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

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


--

Dave Peterson

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

--
John C


"John C" wrote:

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.

--
John C


"Ron Rosenfeld" wrote:

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

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

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)




"excel user" wrote in message
...
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?

"Ron Rosenfeld" wrote:

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

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

And don't forget other countries Chip.

Could add a few more formats<g


Gord

On Wed, 16 Jul 2008 12:32:46 -0500, "Chip Pearson" wrote:

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.

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

"Dave Peterson" wrote:

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




excel user wrote:

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?

"Ron Rosenfeld" wrote:

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

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


--

Dave Peterson