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

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

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

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

Hi Rick

Ahh... Thanks! I understand what your codes does now, it is a worksheet
change event's codes. I actually put the codes into the worksheet's module
like you mentioned.

I was actually looking for help in transforming the existing data
TH00000000789F
in Col.A to TH-00000000789-F. I have managed to write a small code snippet to
produce the result that I want.

Thanks for your help in this.

regards, francis



"Rick Rothstein (MVP - VB)" wrote:

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