Macro to Insert Colon into String

[rudogg]

I am in need of a macro that I can run on a selection of cells, that looks at each cell in the selection, determines if there is an existing colon in the 7th position of the string in the data, and add a second one. If there is no colon, then skip that cell.

103495:AB - would need to look like this 103495::AB

103495:AQ - would need to look like this 103495::AQ

104118:3N:19B - would need to look like this 104118::3N:19B

104118:8N:19B - would need to look like this 104118::8N:19B

CUM4FTHSI63FWH2 would not change at all

Thank you in advance!

 

[Gerald Higgins]

Does it have to be a macro ? You can do this with a fairly simple formula.

 

[Joe4]

Try this code:

Sub MyInsertCode()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Loop through each cell in selection
    For Each cell In Selection
'       Check to see if 7th character is a colon
        If Mid(cell, 7, 1) = ":" Then
'           Update value
            cell.Value = Left(cell, 7) & ":" & Mid(cell, 8)
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[rudogg]

Does it have to be a macro ? You can do this with a fairly simple formula.

It does need to be a macro

 

[rudogg]

Try this code:

Sub MyInsertCode()

    Dim cell As Range
   
    Application.ScreenUpdating = False
   
'   Loop through each cell in selection
    For Each cell In Selection
'       Check to see if 7th character is a colon
        If Mid(cell, 7, 1) = ":" Then
'           Update value
            cell.Value = Left(cell, 7) & ":" & Mid(cell, 8)
        End If
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub

This worked Great!! Thank you for jumping on this so quickly...

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[rudogg]

You are welcome.
Glad I was able to help!

If I could ask you a question, what does this part mean? "& Mid(cell, 8).

 

[Joe4]

If I could ask you a question, what does this part mean? "& Mid(cell, 8).

You combine different strings with the "&" (it is like a "plus" sign for string).

So, we are first taking the first 7 characters of the value in cell, and add an extra colon.
But we then want to add the rest of the string. So we use the MID function to start in the middle of the cell value, specifically, starting at position 8.
In the normal Excel function, you need to tell it how many characters you want in a third argument of the MID function, but in VBA, that argument is optional, and if you leave it off, it returns the entire remaining part of the string (everything from character 8 to the end).

Note that there is a great deal of documentation on all the Excel and VBA functions on-line.
If you do a Google Search on things like "Excel VBA Mid function"), you will see many good links come up.
Here is a good one: Excel VBA Mid Function

 

[bebo021999]

Another solution, with colon could be at any position of string. It searchs for the very first colon.

1) using sub. Hightlight range then run sub:

Option Explicit
Sub Colon()
Dim cell As Range
For Each cell In Selection
    If InStr(1, cell, ":") Then cell.Value = WorksheetFunction.Replace(cell, InStr(1, cell, ":"), 1, "::")
Next
End Sub

2) using function
With A1 is text, type this in B1:
=addcolon(A1)
with below source code:

Option Explicit
Function AddColon(ByVal cell As Range)
If InStr(1, cell, ":") Then
    AddColon = WorksheetFunction.Replace(cell, InStr(1, cell, ":"), 1, "::")
Else: AddColon = cell
End If
End Function

 

[Peter_SSs]

I am in need of a macro that I can run on a selection of cells

If your selection is a contiguous group of cells then another option might be this which does them all at once rather than one cell at a time.

Sub DoubleColon()
  Selection.Value = Evaluate(Replace("if(#="""","""",if(mid(#,7,1)="":"",replace(#,7,1,""::""),#))", "#", Selection.Address))
End Sub