VBA Cell Filtering with conditions

[Craig Freeman]

Hello,

Is there way to have Excel retain everything in a cell that is between
quotation marks ("") and contains an underscore ( _ ) and delete
everything else that does not match this condition. I'd like the
results returned to the column/cell beside the original. The cell
character length is greater that 255 (limitation of countif)? I'm
analyzing SQL statements.

For example: (keep in mind, this example has less than 255 characters
per cell, but I need this to work for cell over 255 characters)

A1
"dog" cat "horse_mule" pig

would return

B1
"horse_mule"


Again, any suggestions/solutions would be greatly appreciated.

 

[Chip Pearson]

Craig,

Try the following function:

Function ParseIt(S As String) As String

Dim Ndx As Long
Dim Arr As Variant
Arr = Split(S, " ")
For Ndx = LBound(Arr) To UBound(Arr)
If Left(Arr(Ndx), 1) = """" And Right(Arr(Ndx), 1) = """"
Then
If InStr(Arr(Ndx), "_") > 0 Then
ParseIt = Arr(Ndx)
Exit Function
End If
End If
Next Ndx

End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Craig Freeman]

Hi Chip,

Thanks for your help, and your function does work, but it seems to only
return the first incident of condition. I was hoping to retrieve all
results that match this condition ("_")with the cell. Is there a way
this function could be modified?

thanks again,
Craig Freeman