Excel VBA Extract Text from a Cell

Question

I have tried looking through Stack Overflow for previous suggestions but haven't found any that have worked.

Here is my situation: I am trying to look at a simple Excel sheet which shows someone's name, position, and then their "Role" which is a custom field I am creating. Right now, I am looking to just do "Engineers" but will also expand to things like "Admin Assistant" and "Manager". (The real spreadsheet is about 8100 lines long).

Here is an example of some test data:

All I need is to scan through the "Title" column, see if it matches a String (in this case, my test string is engineer), and then to copy the String and the remaining I or II or III or in some cases, IV after it.

I have heard about using a regular expression and have used them in SQL before, but am struggling coming up with what I need. Here is my current code where I tried using the MID function:

Sub GetRole()
' Custom function written to take role out of official title

strRole = "Engineer" ' String to check for
Dim lrow As Integer ' Number of Rows
Dim Role As String ' Role to write into adjacent cell

lrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = lrow To 2 Step -1
    If InStr(1, Cells(i, 2), "Engineer") > 0 Then
        Role = Mid(Cells(i,3)), 1, 5)
    Cells.(i, 3).Value = Role
    End If
Next i

End Sub

But that didn't quite work. Any help or advice would be greatly appreciated. I am willing to provide any extra information necessary.

Your `Mid` function is missing arguments and a closing parenthesis. — Taelsin, Nov 20 '17 at 16:53
@Taelsin sorry I didn't write it in here correctly. I'll update it in a second. Still, it isn't pulling what I need it to correctly because the string "Engineer" can be anywhere within the text — artemis, Nov 20 '17 at 16:55
@Taelsin If you look at the example above, the last entry for Jerry Muzkan has "Engineer III" at the beginning. The location of the string to match, "Engineer", could be anywhere in the title. I simply picked a couple that looked easier, but even so, the last example has a different location. — artemis, Nov 20 '17 at 17:07

Tom · Accepted Answer · 2017-11-20T17:29:38.860

1

You can solve this using Regular Expressions. First you need to enable the reference which you do so by going to Tools > References... and enable Microsoft VBScript Regular Expressions 5.5

Then use the following code to generate your answers

Sub GetRole()
    ' Custom function written to take role out of official title

    ' Uncomment the below if using Early Binding i.e. you enable the reference
    ' Dim ReGex As New RegExp
    ' Comment below line if decide to use Late Binding (i.e. you enable the reference)
    Dim ReGex As Object
    Dim i As Long, lrow As Long ' Number of Rows

    ' Comment the below line if decide to use Late Binding (i.e. you enable the reference)
    Set ReGex = CreateObject("VBScript.RegExp")

    With ReGex
        .Global = True
        .IgnoreCase = True
        .Pattern = "(Engineer\sI*\b)"
    End With

    With ActiveSheet
        lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = lrow To 2 Step -1
            If ReGex.test(.Cells(i, 2).Value2) Then .Cells(i, 3).Value2 = Trim(ReGex.Execute(Cells(i, 2).Value2)(0))
        Next i
    End With
End Sub

Generates output:

edited Nov 20 '17 at 17:29

answered Nov 20 '17 at 17:06

Tom

9,725
3
31
48

If I were to write VBA to "sort" through and pull "roles" as a push-button, and gave it to somebody without regexp enabled, would they be able to run this? – artemis Nov 20 '17 at 17:22
If it's enabled them in your workbook, and you give them the whole workbook then yes it should be fine (and they're running it on a windows machine). If you want to just pass them the code you could use Late Binding instead - I'll update my answer to use late binding however, you'll lost the intelliSense – Tom Nov 20 '17 at 17:25
I wasn't sure if the setting applied to the Microsoft Excel program or specifically to the workbook. – artemis Nov 20 '17 at 17:28
Have updated with early binding commented out. Generally speaking it should work if you pass the workbook around, however if say, you send this to a lot of people and they have mixed configs they may have different versions of the reference which could possibly cause an error. Late Binding, while maybe a bit slower should cover this scenario. – Tom Nov 20 '17 at 17:31
thanks for your help. Do you think you could help me understand the syntax for the regexp anymore? What if there is text in the middle such as "Engineer Manufacturer I", but I just want "Engineer I"? Additionally, could you explain .Value2 to me in the if statement? I just want to understand all of this better so one day I can start answering instead of asking :) Thanks! – artemis Nov 20 '17 at 17:58
The RegEx pattern won't pickup "Engineer Manufacturer I" (Didn't include this in your examples). The pattern is set to find `Engineer` then a space then an `I` which could be repeating, with the next character after it has finished repeating being a boundary (i.e. end of line or a space) – Tom Nov 20 '17 at 18:01
`.Value2` gives you basically the raw value of the cell. [Have a look at this link for a bit more info](https://stackoverflow.com/a/17363466/3042759) – Tom Nov 20 '17 at 18:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/159413/discussion-between-tony-kelly-and-tom). – artemis Nov 20 '17 at 18:07
it will probably match "Engineer " from "Engineer Manufacturer I" because of the `I*` instead of `I+` – Slai Nov 20 '17 at 18:07
@Slai you are right, what I meant (and should of said) is it will return `Engineer Manufacturer I` not `Engineer I` – Tom Nov 20 '17 at 18:08

score 1 · Answer 2 · answered Nov 20 '17 at 18:16

1

I think Excel formula will be easier to extend compared to debugging VBA and Regex:

=IF(ISNUMBER(  FIND("Engineer III", E4)), "Engineer III",
 IF(ISNUMBER(  FIND("Engineer II" , E4)), "Engineer II", 
 IF(ISNUMBER(SEARCH("Engineer *I" , E4)), "Engineer I", "")))

answered Nov 20 '17 at 18:16

Slai

22,144
5
45
53

Hey, @Slai - I actually used just the method you have there before, but the overall tool I'm developing for a class project (I always go the extra mile...) does a number of data imports and filtering, this is just one of the filtering steps I needed. Since the goal of the project is "push button", I felt like I was limited to the scope of VBA. – artemis Nov 20 '17 at 18:20

Excel VBA Extract Text from a Cell

2 Answers2