Insert characters into String

[franciz]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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]

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