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

 

[JackDanIce]

Place this bit of code in yours:

MsgBox Unknown

It will tell you the number of characters you are subtracting which I suspect is too small..

 

[johnmerlino]

Yeah that outputs "9". But is there a better way to do this so I can apply this to more than 1 situation?

 

[kpark91]

Try adding

unknown = unknown + 18 'Oops!

It'd be best if

Dim f As String
f = "UNKNOWN SPOUSE OF "
 
...code
unknown = InStr(Range("K" & i), f) + Len(f)
 
...code
 ElseIf unknown <> 0 Then
            LValue = Right(Range("K" & i), Len(Range("K" & i)) - unknown)
 End If
...code

 

[kpark91]

If you want to find statements other than "UNKNOWN SPOUSE OF ", just use the method of Inputbox.

I've noticed in ur result, the punctuation is missing.
I think filter method would work the best in that case.

 

[johnmerlino]

Try adding

unknown = unknown + 18 'Oops!

It'd be best if

Dim f As String
f = "UNKNOWN SPOUSE OF "
 
...code
unknown = InStr(Range("K" & i), f) + Len(f)
 
...code
 ElseIf unknown <> 0 Then
            LValue = Right(Range("K" & i), Len(Range("K" & i)) - unknown)
 End If
...code

Thanks for response. This gives me invalid procedure call or argument.

 

[kpark91]

Could I see your whole code please?
and where has the error occurred?

 

[johnmerlino]

Could I see your whole code please?
and where has the error occurred?

This is whole code:

Sub Inspect()
 
Dim RENums As Object
Dim RENums2 As Object
Dim LValue  As String
Dim LValue2  As String
Dim f As String
f = "UNKNOWN SPOUSE OF "
 
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), f) + Len(f)
   
          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)
          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 for response

 

[johnmerlino]

I believe the error occurs right here:

f = "UNKNOWN SPOUSE OF "

 

[kpark91]

Could it be because of this?

LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos * 2)

it would return subscript out of range error.. so I doubt it..

It very well could be from If statements where you call the objects.
Other than that I don't see any problems with it.

It'd be best if you 'declare/dim' variables which have not been. Altho that doesn't make a huge difference.

and please change unknown initialization to

unknown = InStr(Range("K" & i), f) + Len(f) - 1