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 declaring variables before assigning them.

Dim unknown As Double
Dim LValue As String

and put

Option Explicit

at the top of the module.
This will force you to declare variables before declaring them which is always a good practice.

If this doesn't work, I would suspect the Objects..

I think it has something to do with "+18" part. I dont know why it doesnt like it but if I put a lower number, it doesnt give error. But then it doesnt extract the name either.

 

[johnmerlino]

...#$%^&*(

Could it be... "+17"??? loll

+17 throws error too

 

[kpark91]

Not sure if you've seen my previous post.
so try replacing "+18" with +17...

If this works,.. I dunno.

 

[johnmerlino]

Not sure if you've seen my previous post.
so try replacing "+18" with +17...

If this works,.. I dunno.

It doesnt work with any except 10 and under. But 10 and under dont pull right part of name

 

[kpark91]

This is very bizarre. It is "+17" FOR SURE.

Try replacing it with

LValue = Right$(Range("K" & i).Value, Len(Range("K" & i).Value) - unknown)

 

[johnmerlino]

This is very bizarre. It is "+17" FOR SURE.

Try replacing it with

LValue = Right$(Range("K" & i).Value, Len(Range("K" & i).Value) - unknown)

same error. I know what is trying to be done, it is trying to pull all info after the unknown variable to extract name only. Although it works for some, half way through at a specific cell, thats when error occurs.

 

[kpark91]

What is in that specific cell?

 

[CharlesChuckieCharles]

why not try SUBSTITUTE
as in replace "AS THE UNKNOWN SPOUSE OF" with ""

WorksheetFunction.Substitute Method

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. Syntax
expression.Substitute(Arg1, Arg2, Arg3, Arg4)
expression A variable that represents a WorksheetFunction object.
Parameters
<TABLE><TBODY><TR><TH>Name</TH><TH>Required/Optional</TH><TH width="10%">Data Type</TH><TH>Description</TH></TR><TR><TD class=mainsection>Arg1</TD><TD class=mainsection>Required</TD><TD class=mainsection>String</TD><TD class=mainsection>Text - the text or the reference to a cell containing text for which you want to substitute characters.</TD></TR><TR><TD class=mainsection>Arg2</TD><TD class=mainsection>Required</TD><TD class=mainsection>String</TD><TD class=mainsection>Old_text - the text you want to replace.</TD></TR><TR><TD class=mainsection>Arg3</TD><TD class=mainsection>Required</TD><TD class=mainsection>String</TD><TD class=mainsection>New_text - the text you want to replace old_text with.</TD></TR><TR><TD class=mainsection>Arg4</TD><TD class=mainsection>Optional</TD><TD class=mainsection>Variant</TD><TD class=mainsection>Instance_num - specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.</TD></TR></TBODY></TABLE>
Return Value
String

 

[johnmerlino]

What is in that specific cell?

It seems things came crashing down with this cell:

cell f: HR FORECLOSURE >$50K, <$250K
cell j: DEFENDANT
cell g: BAC HOME V SHAWN BLAKE
cell k: TENANT #2,

Although this one doesnt have the unknown thing, so it might be another. Im still searching

 

[kpark91]

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