Insert "/" into cells that contain data, using a macro

[garfieldmark00]

I need to insert a "/" symbol into multiple cells which already contain data.
For example: the cell already contains 2 letters and 10 digits and I need to
insert the / symbol after the 2 letters and then again after the next 5
digits, so the data changes from aa########## to aa/#####/#####. All the
cells are in one column so it's simple to get a macro to select the column,
but I don't know how to insert characters. Any ideas?
I'm using Excel 2007.

 

[Gary''s Student]

Sub fixum()
s = "/"
For Each r In Selection
v = r.Value
r.Value = Left(v, 2) & s & Mid(v, 3, 5) & s & Right(v, 5)
Next
End Sub

 

[Ron Rosenfeld]

I need to insert a "/" symbol into multiple cells which already contain data.
For example: the cell already contains 2 letters and 10 digits and I need to
insert the / symbol after the 2 letters and then again after the next 5
digits, so the data changes from aa########## to aa/#####/#####. All the
cells are in one column so it's simple to get a macro to select the column,
but I don't know how to insert characters. Any ideas?
I'm using Excel 2007.

Using regular expressions, something like:

==========================
Option Explicit
Sub InsertSlash()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\w{2})(\d{5})"
For Each c In Selection
c.Value = re.Replace(c.Value, "$1/$2/")
Next c
End Sub
=================================


--ron

 

[Rick Rothstein \(MVP - VB\)]

You didn't say if the contents of every cell were of exactly the same shape
(2 letters followed by 10 digits) or not; but your use of the words "for
example" made me think not. So here is a general solution for any number of
leading letters and any number of trailing digits...

Sub InsertSlashes()
Dim C As Range
Dim X As Long
Dim LetterCount As Long
For Each C In Selection
For X = 1 To Len(C.Value)
If Mid$(C.Value, X, 1) Like "#" Then
LetterCount = X - 1
Exit For
End If
Next
If LetterCount > 0 And C.Value Like Replace(String$(LetterCount, _
Chr$(0)), Chr$(0), "[a-zA-Z]") & _
String$(Len(C.Value) - LetterCount, "#") Then
C.Value = Left$(C.Value, LetterCount) & _
Format$(Mid$(C.Value, LetterCount + 1), "!" & _
Replace(String$(-((Len(C.Value) - LetterCount) _
Mod 5 <> 0) + (Len(C.Value) - LetterCount) \ 5, _
Chr$(0)), Chr$(0), "\/&&&&&"))
End If
LetterCount = 0
Next
End Sub

Note: If there aren't any leading letters or any trailing digits or the
shape is anything but leading letters followed by trailing digits, then the
contents of the cell will remain unchanged.

To use this subroutine, select the cells you want to change (do not select
the whole column or you will be waiting quite a long time for the empty
cells to be processed), press Alt+F8 and execute the InsertSlashes macro

Rick

 

[garfieldmark00]

That's exactly what I needed. Many thanks Ron

Using regular expressions, something like:

==========================
Option Explicit
Sub InsertSlash()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\w{2})(\d{5})"
For Each c In Selection
c.Value = re.Replace(c.Value, "$1/$2/")
Next c
End Sub
=================================


--ron

 

[Ron Rosenfeld]

That's exactly what I needed. Many thanks Ron

You're welcome. Glad to help. Thanks for the feedback.

Note that the macro does not check that you have a valid entry. It will work
on any string where 5 digits are preceded by two or more letters or digits (or
underscores).

It would be trivial to change the code to ensure it works only on valid
entries, but more information on what constitutes a valid entry would be
required to do that.

For example, if the ONLY characters in the cell were two initial letters,
without regard to case, followed by 10 digits, then:

=========================
Sub InsertSlash()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(^[A-Za-z]{2})(\d{5})(\d{5})$"
For Each c In Selection
c.Value = re.Replace(c.Value, "$1/$2/$3")
Next c
End Sub
=========================

would process valid entries, and leave invalid entries unchanged.

You could also set conditions such as the relevant string being case sensitive;
being found anywhere within the cell; and you could set results to be just the
relevant processed string; an unprocessed string; an error message if the
pattern is not found; etc.

With all these permutations, I kept it pretty simple for an initial trial.
--ron