custom format

P

Peo Sjoblom

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
 
M

maryj

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?
 
D

Dave Peterson

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
 
M

maryj

Thanks Dave. I was hoping we could do this with just formatting, but I
figured it would involve this type of formula.
 
Top