CODEWITHSUNDEEP
vba
ROD
ROD

Reputation: 23

Remove characters before and after a special character VBA

I have a set of data with strings such as, 1782_eqjobs.hostname, 519_fid_quant.hostname.

I want to be able to keep all of the characters within the first '_' and '.', then remove the rest.

For example;

1782_eqjobs.hostname -> eqjobs

519_fid_quant.hostname -> fid_quant

Is it possible to use a macro to preform this?

Upvotes: 0

Views: 633

Answers (4)

Philip Day
Philip Day

Reputation: 69

You could use a worksheet function, either in the worksheet or in VBA.

Let's assume your strings are in Range A1.

The function in the cell would look like this:

=MID(A1,FIND("_",A1,1)+1,(FIND(".",A1,1)-FIND("_",A1,1))-1)

To use it in VBA you'd put:

worksheetfunction.MID(A1,FIND("_",A1,1)+1,(FIND(".",A1,1)-FIND("_",A1,1))-1)

Hope that helps!

Phil

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You can use a regex pattern of 

_(.*)\.

and then extract group 1 of match. This is a little fragile as is based on all strings being in the layout your provided without multiple instances of _ some text .

Try it

I mean an implementation such as:

Option Explicit
Public Sub test()

    Debug.Print GetString("1782_eqjobs.hostname")

End Sub

Public Function GetString(ByVal inputString As String) As Variant
    Dim matches As Object

    With CreateObject("vbscript.regexp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "_(.*)\."
        If .test(inputString) Then
            Set matches = .Execute(inputString)
            GetString = matches(0).SubMatches(0)
            Exit Function
        End If
    End With
    GetString = CVErr(xlErrNA)
End Function

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

This simple formula will do it:

=MID(LEFT(A1,FIND(".",A1)-1),FIND("_",A1)+1,LEN(A1))

enter image description here

Upvotes: 1

mrkrister
mrkrister

Reputation: 67

Maybe..

Sub CleanUp()
    On Error Resume Next
    For Each c In Selection
        c.Value = Mid(c.Value, InStr(1, c.Value, "_") + 1, InStr(1, c.Value, ".") - InStr(1, c.Value, "_") - 1)
    Next c
End Sub

Upvotes: 0

Related Questions