VBA extract part of string using a specific word

[decadence]

Hi, Is it possible to extract part of a string using a specific word but return the length of the text.
There will always be numbers before the word and is not case sensitive.

Example:

The Text I want to extract is the length of "way"

Something 40way Nothing	40way
Is it 12 way	12 way
20WAY is found	20WAY
This 18 Way is here	18 Way

<tbody>
</tbody>

 

[MickG]

Try this UDF:-

Function Way(txt [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 sp = Split(txt, " ")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
    [COLOR="Navy"]If[/COLOR] InStr(UCase(sp(n)), "WAY") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Len(sp(n)) = 3 [COLOR="Navy"]Then[/COLOR]
            Way = sp(n - 1) & " " & sp(n)
        [COLOR="Navy"]Else[/COLOR]
            Way = sp(n)
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[decadence]

Hi MickG, How would i incorporate this into a sub

 

[MickG]

Perhaps like this for Data in column "A".

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Offset(, 1).Value = Way(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Function Way(txt [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 sp = Split(txt, " ")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
    [COLOR="Navy"]If[/COLOR] InStr(UCase(sp(n)), "WAY") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Len(sp(n)) = 3 [COLOR="Navy"]Then[/COLOR]
            Way = sp(n - 1) & " " & sp(n)
        [COLOR="Navy"]Else[/COLOR]
            Way = sp(n)
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[decadence]

Thank you, this is what I need. Sorry for late reply

 

[MickG]

You're welcome

 

[Peter_SSs]

Another way (excuse the pun)

Sub LenWay()
  Dim c As Range
  
  With CreateObject("VBSCript.RegExp")
    .IgnoreCase = True
    .Pattern = "\d+ *way"
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If .Test(c.Value) Then c.Offset(, 1).Value = .Execute(c.Value)(0)
    Next c
  End With
End Sub

Note that my code & Mick's may not produce the same results as mine specifically looks for digits before the "way" so if a cell contained "Anyway it is 12way", Mick's code will return "Anyway" whereas mine will return "12way"

 

[decadence]

Hi Peter_SSs, I think this may be the best way forward for the end user, Thanks for your help

 

[Peter_SSs]

Hi Peter_SSs, ... Thanks for your help

You are welcome.