CODEWITHSUNDEEP
excelvba
Matt Ledger
Matt Ledger

Reputation: 213

Insert colons into numbers in string

I have ~300 strings in an Excel spreadsheet.

Each are of varying length but all take the same form:

0033",@"0103",@"0133",@"0203",@"0233", nil

I need to add colons to the centre of each number, for instance the example above would become:

00:33",@"01:03",@"01:33",@"02:03",@"02:33", nil

I have researched various VBA functions but I can't find any that would add a character to a string. Perhaps there is a simpler way of doing this in Excel?

Upvotes: 2

Views: 1557

Answers (1)

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

Add this function in a module.

Function InsertColon(ByVal original As String) As String
    Dim re As Object
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(\d\d)(\d\d)"
    re.Global = True
    InsertColon = re.Replace(original, "$1:$2")
End Function

Now you can easily use it as a formula. e.g. If cell A1 contains your original string, and you want to show the formatted value in cell A2, then put the following formula in cell A2:

=InsertColon(A1)

Upvotes: 6

Related Questions