none
Excel Regular Expressions?

    Question

  • I have a text file with ip addresses in [111.222.33.4] format.  However, I want to change this into 111.222.033.004 format.  In the past I have used MS regular expressions in Word successfuly,  I have tried to use the same in Excel and they do not work.  What is the best way to change this format for the IP address? 

    PS - At work we have the default VBE that comes with Excel 2007 and we cann ot install any other tools or libraries.

    Thank you in advance.

    Thursday, October 21, 2010 6:32 PM

All replies

  • Hi brownchap,

     

    One thing that came up to my mind is to use a split into this string by dots and then fulfill each value with zeroes. Then, get the value back as string.

     

    It would be st like this..

     

    Function myFormat(ByVal sMyValue As String) As String
      
      Dim sResult As String
      Dim vValues As Variant
      Dim vItem As Variant
      
      vValues = Split(sMyValue, ".")
      
      For Each vItem In vValues
        
        sResult = sResult & Right("000" & vItem, 3) & "."
        
      Next vItem
      
      myFormat = Left(sResult, Len(sResult) - 1)
    
    End Function

     

    hope it helps!

    • Proposed as answer by TiagoCardoso Thursday, October 21, 2010 8:00 PM
    Thursday, October 21, 2010 6:53 PM
  • Hello tdcardoso,

    Thank you for this code snippet!  It worked like a champ.

    How long did you code in VBA and VB to get good at it?  I am starting out (after 15 year break from coding) in Windows.

    Thank you once again.

    Friday, October 22, 2010 3:09 PM
  • Hello tdcardoso,

    Thank you for this code snippet!  It worked like a champ.

    How long did you code in VBA and VB to get good at it?  I am starting out (after 15 year break from coding) in Windows.

    Thank you once again.

    Hello chap,

    I'm working with VB / VBA since '05, I guess.. but that's my first time in a VBA forum. Love to help :D

    My manager is an amazing developer (code lover) so it's easy to learn with him.. hehe. My suggestion is to always be in touch with good dev's (and keep away of the bad ones) :)

    Cheers!

    Friday, October 22, 2010 3:26 PM
  • Just so you can see that there is almost always more than one way to code a solution, here is another function for you to consider...

    Function IPformat(IPaddress As String) As String
      Dim X As Long, Parts() As String
      IPformat = "000.000.000.000"
      Parts = Split(IPaddress, ".")
      For X = 0 To 3
        Mid(IPformat, 1 + 4 * X, 3) = Format$(Parts(X), "000")
      Next
    End Function

    Note that instead of repeatedly concatenating text together, I use a "string stuffing" technique (where I use Mid as a statement instead of as a function). While it won't matter for such a small string, "string stuffing" is a much faster technique (when a problem lends itself to its use) than repeated concatenations (which can be important in large looping code). For your learning experience, also note the use of the Format function. Assuming you might want to ask me the same question regarding experience... I have been programming in BASIC and VB (along with some other languages along the way) since 1981.

    Saturday, October 23, 2010 8:32 AM
  • I have a text file with ip addresses in [111.222.33.4] format.  However, I want to change this into 111.222.033.004 format.  In the past I have used MS regular expressions in Word successfuly,  I have tried to use the same in Excel and they do not work.  What is the best way to change this format for the IP address? 

    PS - At work we have the default VBE that comes with Excel 2007 and we cann ot install any other tools or libraries.

    Thank you in advance.


    It is possible to use the Regular Expressions with Excel, although the other solutions proposed here would be more efficient for this problem.  But perhaps this example may help you in the future:

    Option Explicit
    Sub IP()
        Const s As String = "111.222.33.4"
       
    Debug.Print fmtIP(s)
    End Sub
    '------------------------------------------
    Function fmtIP(s As String) As String
        Dim re As Object, mc As Object
        Dim sm As Object, i As Long
        Dim sTemp(0 To 3) As String
    Set re = CreateObject("vbscript.regexp")
        re.Global = True
        re.Pattern = "\b(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})\b"
    If re.test(s) = True Then
        Set mc = re.Execute(s)
        Set sm = mc(0).submatches
        For i = 0 To sm.Count - 1
            sTemp(i) = Format(sm(i), "000")
        Next i
        fmtIP = Join(sTemp, ".")
    End If
    End Function

    Ron
    Saturday, October 23, 2010 11:32 PM