Custome Format Alpha

[Rick]

Is there anyone out there that can tell me how to create a format that does
this:
07-108029827-A it's the A that needs to be a substitue alpa character,
because the A may have to be a B, or C, or D.

Thanks

 

[Rick Rothstein \(MVP - VB\)]

Is the "number" part always going to be 07-108029827? In other words, what
do you want to TYPE into the cell and what do you want appear? If the number
part is not the same all the time, where is it coming from?

Rick

 

[Rick]

The number part as well as the alpha part can be anynumber and any alpha. It
is a trace code for medical billing, but the format will always be the same.

 

[Rick Rothstein \(MVP - VB\)]

Then I guess I am not following what you want or need. What are you
physically going to type into the cell you asked to be able to format? Where
does the A, B, C, D you asked about come from? You are going to have to give
us a little more detail about your layout, what is typed where, what you
want to look like what where, etc.

Rick

 

[Peter T]

Try and describe what you have and what you have want to achieve.

Regards,
Peter T

 

[Rick]

Rick:
I will be typing the entire number and letter in the cell, I have the first
part of the format which is 00-000000000-? the question mark is what is
missing. what character do I use to have a single alpha appear in that
position.

 

[Rick]

To Rick & Peter:
I have a cell that is for entry by keyboard. It will contain a billing
reference number, that has a format of 2digits, a dash, 9digits, a dash, and
a alpha character a thru z. All I want to do is enter that code as
follows:12123456789a, and have it show in the cell as 12-123456789-A, or
12-123456789-B, or 12-123456789-Z

 

[Ron Rosenfeld]

Is there anyone out there that can tell me how to create a format that does
this:
07-108029827-A it's the A that needs to be a substitue alpa character,
because the A may have to be a B, or C, or D.

Thanks

You can't have a variable letter character as part of a cell number format,
without using a VBA Macro to actually modify the cell format.

If you are typing the characters into some cell, all at once with no spaces or
hyphens, you could use a formula in an adjacent cell to have it look the way
you want.

=TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1)

You could even do some validation:

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISTEXT(RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

--ron

 

[Rick Rothstein \(MVP - VB\)]

Ah, now I see what you want. Okay, I don't think you can do this with a cell
format, but you can do it with a worksheet event procedure. Right-click the
tab for the sheet where you want this functionality to be on and copy/paste
this code into the code window that appears....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = Format(UCase(Target.Value), "&&-&&&&&&&&&-&")
Whoops:
Application.EnableEvents = True
End Sub

Note that I set this up for Column D (that is what the '<> 4' test is doing)
but you can change this as needed. Now, go back to your sheet and type in
one of your numbers into a cell in Column D (don't type in any dashes, the
code will insert them for your)... hit Enter, Tab or click into a different
cell and watch what happens to your entry.

Rick

 

[Ron Rosenfeld]

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISTEXT(RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

Better with:

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISERR(-RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

--ron

 

[Rick]

Thanks Ron, Rick, Peter:
I'll give that a try.

Better with:

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISERR(-RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

--ron

 

[Rick]

Just out of curosity, is there a custom alpha only format that can be stored
in the customs section of cell formats.

 

[Peter T]

Although a cell numberformat can display alpha's within the format, the cell
contents must be a real number without any alpha's, so your last variable
alpha precludes Numberformat being of any use

Try the following in the sheet module. Rt-click the sheet tab, View code,
and paste into the sheet module.

Change "A1:A10" to the cell or block of cells you want to process.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sOrig As String, sNum As String, sNew As String
Dim rng As Range, cell As Range

On Error GoTo errExit
Set rng = Intersect(Range("A1:A10"), Target)
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
sOrig = cell.Value
sNew = Replace(sOrig, "-", "")
sNew = Replace(sNew, "#", "")
sNum = Int(Val(sNew))
sNew = UCase(Mid(sNew, Len(sNum) + 1, 1))
If UCase$(sNew) = LCase(sNew) Then sNew = ""
sNew = sNew & "?"
sNum = Left$(sNum & "#########", 9)
sNum = Left$(sNum, 2) & "-" & Right$(sNum, 7)
sNew = sNum & Left$(sNew, 1)
If sNew <> sOrig Then cell = sNew ' only if necessary
resNext:
Next
End If

errExit:
Application.EnableEvents = True

End Sub


As written, #'s are included in case of missing numerals, and a ? for a
missing last alpha.

If(?) it does roughly what you want I have no doubt it could be made more
efficient with fewer lines of code

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Okay, Peter's posting includes some error checking, so I decided to
incorporate error checking into my offering as well. Remove the code I gave
you earlier and copy/paste this into its place instead...

' ******* Start of Code *******
Dim LastCellVisited As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetStrippedOfDashes As String
If Target.Column <> 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
TargetStrippedOfDashes = UCase(Replace(Target.Value, "-", ""))
If TargetStrippedOfDashes Like "###########[A-D]" Then
Target.Value = Format(TargetStrippedOfDashes, "&&-&&&&&&&&&-&")
ElseIf Len(Target.Value) > 0 Then
LastCellVisited.Select
MsgBox "You last attempted entry was not properly formed"
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set LastCellVisited = Target
End Sub
' ******* End of Code *******

If you make an invalid entry, the code will return you to the cell with the
invalid entry and pop up a MessageBox advising you of the situation. Also,
you can now type in a value with one, two or no dashes (doesn't matter if
any entered dashes are placed correctly or not as I remove them and insert
properly located dashes... all that matters is that your entry has 11 digits
followed one of the letters A, B, C, D, in either upper or lower case, after
the dashes are removed).

Rick

 

[Peter T]

Oops, I didn't absorb the described objective. Hopefully this works will
convert:

12123456789a
to
12-123456789-A

and
12123456
to
12-123456###-?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sOrig As String, sNum As String, sNew As String
Dim rng As Range, cell As Range

On Error GoTo errExit
Set rng = Intersect(Range("A1:A10"), Target)
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
sOrig = cell.Value
sNew = Replace(sOrig, "-", "")
sNew = Replace(sNew, "#", "")
sNum = Int(Val(sNew))
sNew = UCase(Mid(sNew, Len(sNum) + 1, 1))
If UCase$(sNew) = LCase(sNew) Then sNew = ""
sNew = "-" & sNew & "?"
sNum = Left$(sNum & "###########", 11)
sNum = Left$(sNum, 2) & "-" & Right$(sNum, 9)
sNew = sNum & Left$(sNew, 2)
If sNew <> sOrig Then cell = sNew ' only if necessary
Next
End If

errExit:
Application.EnableEvents = True

End Sub

Regards,
Peter T