using vba to extract characters within a string

[johnmerlino]

Hey all,

I have a cell like this:

_AS THE UNKNOWN SPOUSE OF KATARINA ASKI,

I have this:

...code
unknown = InStr(Range("K" & i), "UNKNOWN SPOUSE OF ")

...code
 ElseIf unknown <> 0 Then
            LValue = Right(Range("K" & i), Len(Range("K" & i)) - unknown)
 End If
...code

That above statement returns:

S THE UNKNOWN SPOUSE OF KATARINA ASKI,

Rather than

KATARINA ASKI

thanks for response

 

[johnmerlino]

Try

Sub Inspect()
 
Dim RENums As Object
Dim RENums2 As Object
Dim LValue  As String
Dim LValue2  As String
 
Set RENums = CreateObject("VBScript.RegExp")
Set RENums2 = CreateObject("VBScript.RegExp")
 
 
RENums.Pattern = "DEFENDANT"
RENums2.Pattern = "FORECLOSURE"
 
  Dim lngLastRow As Long
  lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
 
 
  Dim i
 
  For i = 1 To lngLastRow
 
    If RENums2.test(Range("F" & i).Value) Then
 
      If RENums.test(Range("J" & i).Value) Then
 
          pos = InStr(Range("G" & i), " V ")
 
          pos2 = InStr(Range("G" & i), " VS ")
 
          pos3 = InStr(Range("G" & i), " V ESTATE OF ")
 
          dbspace = InStr(Range("K" & i), "  ")
 
          unknown = InStr(Range("K" & i), "UNKNOWN SPOUSE OF ")
          'If not found
          If unknown <> 0 Then
              unknown = unknown + 17
          End If
 
 
          If pos3 <> 0 Then
             LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos * 2)
          ElseIf pos <> 0 Then
             LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos - 2)
          ElseIf pos2 <> 0 Then
            LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos - 2)
          End If
 
          If dbspace <> 0 Then
            LValue = Range("K" & i)
          ElseIf unknown <> 0 Then
            LValue = Right(Range("K" & i), Len(Range("K" & i)) - unknown)
          Else
            LValue = "" 'if the cell doesnt have two spaces or the text unknown spouse of, then we make the cell output empty
          End If
 
 
            schr = Right(LValue, 1)
            If schr = "_" Then
              With WorksheetFunction
               Range("N" & i).Value = Trim(.Substitute(LValue, "_", ""))
              End With
            Else
              Range("N" & i).Value = Trim(LValue)
            End If
            Range("O" & i).Value = Trim(LValue2)
 
 
      End If
    End If
 
  Next i
End Sub

Thanks, that worked.

 

[kpark91]

np :