Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCH but for cell contents rather than text | Excel Discussion (Misc queries) | |||
Can I search for the displayed value in a cell, not the contents? | Excel Discussion (Misc queries) | |||
search if cell contents exist in other cells | Excel Worksheet Functions | |||
search Sheet2! for the contents of Sheet1! | Excel Discussion (Misc queries) | |||
Search Through Multiple Tabs' Contents | New Users to Excel |