CODEWITHSUNDEEP
excelexcel-formulavba
user1902849
user1902849

Reputation: 1141

Function to remove any characters before a first number in a string and any characters after dot/semicolon

How to enhance this function to exclude remove any characters before a first number in a string and any characters after . or : ? for instance:

GigabitEthernet0/3.210 --> 0/3  
Serial6/2:0.100 --> 6/2  
Serial6/6:0 --> 6/6

Function: =REPLACE($A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A2&"0123456789"))-1,"")

Upvotes: 0

Views: 918

Answers (2)

Captain
Captain

Reputation: 2218

I would solve this with regular expressions - it would allow more flexibility in the rules, if that were ever needed.

E.g. using the following vba code to expose REGEXP:

Function RegExp(ByVal repattern As String, ByVal value As String, Optional occurrence = 1)
' does a regular expression search for "repattern" in string "value"
' returns the match
'  or "" if not found

  RegExp = ""
  Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
  ' create the RegExp Object with late binding
  Set RegEx = CreateObject("vbscript.regexp")
  With RegEx
    .Global = True      'look for global matches
    .Pattern = repattern
  End With
  counter = 1
  Set RegMatchCollection = RegEx.Execute(value)
  For Each RegMatch In RegMatchCollection
    If counter = occurrence Then
      RegExp = RegMatch.value
      Exit For
    Else
      counter = counter + 1
    End If
  Next
  Set RegMatchCollection = Nothing
  Set RegEx = Nothing
End Function

You could then have the formula on the worksheet like =RegExp("[0-9][^\.:]*",A1)

Upvotes: 0

Jerry
Jerry

Reputation: 71578

You can use what you already have to get that, and use the MID function instead of replace:

=MID(
     $A1,
     MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),
     MIN(FIND({":","."},$A1&".:"))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))
)

Upvotes: 1

Related Questions