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!

 

[rudogg]

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

This was a great explanation!! Thank you for taking the time to respond...