Home |
Search |
Today's Posts |
#1
|
|||
|
|||
custom format
What would the custom format be for something like this:
CL-123-4567 -- maryj |
#2
|
|||
|
|||
That is not a number, it is text, AFAIK you can customize text format,
however if it always starts with CL and then you want 3 numbers dash and 4 numbers you can use "CL-"000-0000 and type in 1234567 and it will come out that way Regards, Peo Sjoblom "maryj" wrote: What would the custom format be for something like this: CL-123-4567 -- maryj |
#3
|
|||
|
|||
The letters at the beginning may not always be the same. Also, did you mean
to say that this can or can not be done with text? -- maryj "Peo Sjoblom" wrote: That is not a number, it is text, AFAIK you can customize text format, however if it always starts with CL and then you want 3 numbers dash and 4 numbers you can use "CL-"000-0000 and type in 1234567 and it will come out that way Regards, Peo Sjoblom "maryj" wrote: What would the custom format be for something like this: CL-123-4567 -- maryj |
#4
|
|||
|
|||
Number formatting means numbers.
But you could use another cell (column of cells) that does your formatting. If you type this in A1: cl1234567 you could use a formula like: =upper(left(a1,2))&"-"&mid(a1,3,3)&"-"&mid(a1,6,4) (and copy down) In fact, you could set up a few rows: =if(a1="","",upper(left(a1,2))&"-"&mid(a1,3,3)&"-"&mid(a1,6,4)) Another option would be to use an event macro that looks for data in your range and fixes it the way you specify. If you want to try that, this assumes that the strings are always 9 characters and you're typing them anywhere in column A: Just rightclick on the worksheet tab that should have this behavior. Select View code and paste this in the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler: With Target If .Cells.Count 1 Then Exit Sub If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then Exit Sub If .HasFormula Then Exit Sub If Len(.Value) < 9 Then Exit Sub Application.EnableEvents = False .Value = UCase(Left(.Value, 2)) & "-" & Mid(.Value, 3, 3) _ & "-" & Mid(.Value, 6, 4) End With errHandler: Application.EnableEvents = True End Sub maryj wrote: The letters at the beginning may not always be the same. Also, did you mean to say that this can or can not be done with text? -- maryj "Peo Sjoblom" wrote: That is not a number, it is text, AFAIK you can customize text format, however if it always starts with CL and then you want 3 numbers dash and 4 numbers you can use "CL-"000-0000 and type in 1234567 and it will come out that way Regards, Peo Sjoblom "maryj" wrote: What would the custom format be for something like this: CL-123-4567 -- maryj -- Dave Peterson |
#5
|
|||
|
|||
Thanks Dave. I was hoping we could do this with just formatting, but I
figured it would involve this type of formula. -- maryj "Dave Peterson" wrote: Number formatting means numbers. But you could use another cell (column of cells) that does your formatting. If you type this in A1: cl1234567 you could use a formula like: =upper(left(a1,2))&"-"&mid(a1,3,3)&"-"&mid(a1,6,4) (and copy down) In fact, you could set up a few rows: =if(a1="","",upper(left(a1,2))&"-"&mid(a1,3,3)&"-"&mid(a1,6,4)) Another option would be to use an event macro that looks for data in your range and fixes it the way you specify. If you want to try that, this assumes that the strings are always 9 characters and you're typing them anywhere in column A: Just rightclick on the worksheet tab that should have this behavior. Select View code and paste this in the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler: With Target If .Cells.Count 1 Then Exit Sub If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then Exit Sub If .HasFormula Then Exit Sub If Len(.Value) < 9 Then Exit Sub Application.EnableEvents = False .Value = UCase(Left(.Value, 2)) & "-" & Mid(.Value, 3, 3) _ & "-" & Mid(.Value, 6, 4) End With errHandler: Application.EnableEvents = True End Sub maryj wrote: The letters at the beginning may not always be the same. Also, did you mean to say that this can or can not be done with text? -- maryj "Peo Sjoblom" wrote: That is not a number, it is text, AFAIK you can customize text format, however if it always starts with CL and then you want 3 numbers dash and 4 numbers you can use "CL-"000-0000 and type in 1234567 and it will come out that way Regards, Peo Sjoblom "maryj" wrote: What would the custom format be for something like this: CL-123-4567 -- maryj -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell will not format numbers correctly for a 13 digit custom barc. | Excel Worksheet Functions | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How do I format a cell for a custom part number? | Excel Discussion (Misc queries) | |||
Custom Format for ¢ | Excel Discussion (Misc queries) | |||
Custom Time Format doesn't work for me | Excel Discussion (Misc queries) |