Remove characters before and after a special character VBA

Question

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?

See [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — Comintern, Nov 08 '18 at 15:03
yes vba will do it. So will a formula. There are many ways that this can be done. — Scott Craner, Nov 08 '18 at 15:03

QHarr · Answer 1 · 2018-11-08T15:32:45.027

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

I'm sorry but i'm completely new to using VBA would you be able to explain what you mean? — ROD, Nov 08 '18 at 15:14

score 1 · Accepted Answer · answered Nov 08 '18 at 15:24

1

This simple formula will do it:

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

answered Nov 08 '18 at 15:24

Scott Craner

148,073
10
49
81

score 0 · Answer 3 · answered Nov 08 '18 at 15:22

0

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

answered Nov 08 '18 at 15:22

mrkrister

67
1
6

score 0 · Answer 4 · edited Nov 08 '18 at 15:34

0

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

edited Nov 08 '18 at 15:34

Scott Craner

148,073
10
49
81

answered Nov 08 '18 at 15:31

Philip Day

69
7

1

`worksheetfunction.MID(A1,FIND("_",A1,1)+1,(FIND(".",A1,1)-FIND("_",A1,1))-1)` will not work. First `Mid` is a natural vba function. Second all the `Find` would need the `worksheetfunction.` each. But why not use `INSTR` instead? But then you would have @mrkrister's answer. – Scott Craner Nov 08 '18 at 15:36
I have since learnt much, and I agree that my previous answer was terrible. I must apologize all. Thank you @ScottCraner for correcting me. – Philip Day Mar 12 '19 at 16:17

Remove characters before and after a special character VBA

4 Answers4