VBA Find first word before a character and set as word found to match againist sheet name

[decadence]

Hi, I am trying to find the first word in a cell before an Underscore, the words I need to find are varying length including numbers before the underscore.
I don't want to extract the word, I just want to find the first occurrence of the word and set this word as the word found so I can use it to match it against a sheet name.
I haven't been able to find a solution to this but I know either regex or split is what I need to use to find the word but I haven't got a clue where to start with this. can anyone help with this please.

 

[mumps]

Let's assume you have the following string in cell A1: hello out there_how are you
Try this macro:

Sub test()
    Dim x As Variant
    Dim y As Variant
    x = Split(Range("A1"), "_")
    y = Split(x(0), " ")
    MsgBox y(UBound(y))
End Sub

 

[decadence]

Hi Mumps, This works great for [a1] but how would I do this for a set range so that it only finds the first occurrence of the word even if there are multiple occurrences in that range i.e. range("B2:B1000")

 

[mumps]

Are you looking for a specific word or do you want the word before the underscore from every cell in the range? Can you post a few examples from your range and explain in detail using those examples.

 

[decadence]

This is what I have so far but it will find any word even if there is not an underscore and will error if nothing is in the cell

    Dim Rng As range, i As range, x As Variant, y As Variant, c As range, j As String
    Application.ScreenUpdating = False
    For Each i In range("A1:Z2")
    Select Case i.value2
    Case "Part", "Ordered" "Requested"
        If Not Rng Is Nothing Then
            Set Rng = Union(Rng, i)
        Else
            Set Rng = i
        End If
            End Select
        Next
    If Rng Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Not Rng Is Nothing Then
        Set Rng = Rng.Resize(Cells(Rows.Count, Rng.Column).End(xlUp).Row).Offset(1)
    For Each c In Rng
        x = Split(c, "_")
        y = Split(x(0), " ")
        MsgBox y(UBound(y))
    Exit For
        Next c
    End If
End Sub

Below is an example of what I want to find before the underscore so I can match the found word to a sheet name, but only If the first occurrence of the word is found then set that as the string to match to the sheet name

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]ThisPart1_123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ThatPart1_456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ThisPart1_789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Below is an example of what I want to find before the underscore so I can match the found word to a sheet name, but only If the first occurrence of the word is found then set that as the string to match to the sheet name

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]ThisPart1_123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ThatPart1_456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ThisPart1_789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Some questions...

1) Are there any spaces in your Parts names? In other words, could one of your cells contain a value like this...

Some Other Part_987

2) Could there be more than one underscore character in a cell? If so, which word did you want... the one before the first or the last underscore?

3) If there is only one underscore in a cell, does it appear before the a number which is always at the end of the text like your examples show?

 

[decadence]

Hi Rick, There will be no spaces and there will only ever be one underscore and will always be like the examples shown. the only thing that will vary is the word found before the underscore and the number after the underscore.
The text found before the underscore may vary in length and may or may not contain numbers, but the text will match a sheet name in another workbook containing 100+ sheets to match against, and I would also like to activate that sheet after sheet match

 

[Rick Rothstein]

Hi Rick, There will be no spaces and there will only ever be one underscore and will always be like the examples shown. the only thing that will vary is the word found before the underscore and the number after the underscore.
The text found before the underscore may vary in length and may or may not contain numbers, but the text will match a sheet name in another workbook containing 100+ sheets to match against, and I would also like to activate that sheet after sheet match

Okay, in reading through all your message, it appears that you are looking to obtain a list of the unique part descriptions, that is the text before the underscore, ignoring any cells where the text does not have an underscore. If so, then you can use this function to return a zero-based array of such unique part descriptions which you can then iterate within your own code, one at a time, and do whatever you need to with each of them.

Function Parts() As Variant
  Dim R As Long, X As Long, Data As Variant
  Data = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Data)
      If InStr(Data(R, 1), "_") Then .Item(Split(Trim(Data(R, 1)), "_")(0)) = 1
    Next
    Parts = .Keys
  End With
End Function

Put this function in a general module (same kind of code window you put macros in) and simply assign its name to a variant variable. For example, if your variant variable's name is Arr, then you would write...

Arr = Parts()

 

[decadence]

This is perfect, Thank you. How would I match the Arr found against a sheet name in another workbook then activate that sheet?