Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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)" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 "Rick" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
Try and describe what you have and what you have want to achieve.
Regards, Peter T "Rick" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 Rothstein (MVP - VB)" wrote: 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 "Rick" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 "Peter T" wrote: Try and describe what you have and what you have want to achieve. Regards, Peter T "Rick" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
On Sun, 9 Mar 2008 11:22:00 -0700, Rick wrote:
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 "Rick" wrote in message ... 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 Rothstein (MVP - VB)" wrote: 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 "Rick" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
On Sun, 09 Mar 2008 16:19:57 -0400, Ron Rosenfeld
wrote: =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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
Thanks Ron, Rick, Peter:
I'll give that a try. "Ron Rosenfeld" wrote: On Sun, 09 Mar 2008 16:19:57 -0400, Ron Rosenfeld wrote: =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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
Just out of curosity, is there a custom alpha only format that can be stored
in the customs section of cell formats. "Rick" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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" wrote in message ... 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 "Peter T" wrote: Try and describe what you have and what you have want to achieve. Regards, Peter T "Rick" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "Rick" wrote in message ... 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 Rothstein (MVP - VB)" wrote: 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 "Rick" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custome Format Alpha
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 "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 "Peter T" wrote: Try and describe what you have and what you have want to achieve. Regards, Peter T "Rick" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to format a cell to keep alpha characters only? | Excel Discussion (Misc queries) | |||
Custome Format | Excel Discussion (Misc queries) | |||
Alpha Charachters in Format Cells Custom | Excel Worksheet Functions | |||
How do I format a column in alpha order? | New Users to Excel | |||
Custome Number format based on Value | Excel Discussion (Misc queries) |