Search Cell Contents Between ||
 

I have cells that contain a list seperated by || (i.e.
|cat|dog|fish|ape|). I am looking for options to search thru this list
for a particular item. I know I can to a text to column and then
search each of the resulting individual cells for say dog, but I am
looking for an easier way. The problem with text to column is that
some of the cells have only one item whereas others have 8, so it gets
messy. Using a formula, can I chop up the string into an array and
then search the array elements for a particular string?

-Andrew V. Romero

Search Cell Contents Between ||
 

You might try some code like the following:


Dim Rng As Range
Dim Arr As Variant
Dim S As String
Set Rng = Range("A1")
S = Trim(Rng.Text)
If Left(S, 1) = "|" Then
S = Mid(S, 2)
End If
If Right(S, 1) = "|" Then
S = Left(S, Len(S) - 1)
End If
Arr = Split(S, "|")
Range("B1").Resize(1, UBound(Arr) - LBound(Arr) + 1).Value = Arr




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


wrote in message
oups.com...
I have cells that contain a list seperated by || (i.e.
|cat|dog|fish|ape|). I am looking for options to search thru
this list
for a particular item. I know I can to a text to column and
then
search each of the resulting individual cells for say dog, but
I am
looking for an easier way. The problem with text to column is
that
some of the cells have only one item whereas others have 8, so
it gets
messy. Using a formula, can I chop up the string into an array
and
then search the array elements for a particular string?

-Andrew V. Romero

Search Cell Contents Between ||
 

I got to thinking more, and I can probably just use the search formula,
then it returns a number, I know I found the string I was looking for.
If it returns a null, my item was not found. Thanks for the code , I
will also take a look at it.

-Andrew V. Romero