I'm not sure I understand your use of the term "sheet's level module"... to
me a Module is something you get when you click the VBE's menu bar
(Insert/Module)... that would be the wrong place for it. The code goes into
the worksheet's code window. Usually I get there by right-clicking the
worksheet's tab and selecting View Code from the popup menu. Copy/Paste the
code (event procedure header and all) into the code window that appears when
you do that.

A side note: In case that is where you actually put it, I should point out
it will only do something when the number of characters entered
(**excluding** any and all dashes) is 14. So, if you put your posted text of

TH00000000789F

into a cell in Column A, it should automatically reformat as you wish. You
could also put

TH--0--000---00007--89F

into Column A and it will also format correctly as it has 14 characters in
it **after** you remove all the dashes.

Rick


"franciz" wrote in message
...
Hi Rick

How does your codes works. i have place this into a sheet's level module
but
nothing happen.

Not sure where I have went wrong.

thanks

regards, francis

"Rick Rothstein (MVP - VB)" wrote:

First off, your String values appear to be 14 characters long, not 12
characters long as you stated. Assuming 14 characters is correct, give
this
Workbook_Change event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 1 Then
If Len(Replace(.Value, "-", "")) = 14 Then
On Error GoTo Whoops
Application.EnableEvents = False
.Value = Format(Replace(.Value, "-", ""), "@@-@@@@@@@@@@@-@")
End If
End If
End With
Whoops:
Application.EnableEvents = True
End Sub

Rick


"franciz" wrote in message
...
Hi

I have string, eg. TH00000000789F in a range, let say in column A, how
do
I
insert "-" after the first 2 characters and before the last character
resulting
TH-00000000789-F. The string are always 12 Characters.

Thanks in advance

regards, francis