Finding sub strings

[noclass1980]

I want to search column C on for a specific string. The cells in column C can hold variable length strings such as
C1: AAB1,
C2: AB1, B1, C1
C3: D2, E3
etc.
I want to find the first instance of a specific string and then copy cells around it. The problem I have is that, the use search doesn't seem to find the required string if that string is also part of another longer string. For example, I want to find string AB1 but the search returns the first instance of AB1 in the cell C1, rather than the required one in cell C2. Is there a way to look for the required string even though it may appear within a different, longer string? All the individual strings within a cell are comma delimited if there is more than one string.
Thanks in advance.

 

[Rick Rothstein]

I want to search column C on for a specific string. The cells in column C can hold variable length strings such as
C1: AAB1,
C2: AB1, B1, C1
C3: D2, E3
etc.
I want to find the first instance of a specific string and then copy cells around it. The problem I have is that, the use search doesn't seem to find the required string if that string is also part of another longer string. For example, I want to find string AB1 but the search returns the first instance of AB1 in the cell C1, rather than the required one in cell C2. Is there a way to look for the required string even though it may appear within a different, longer string? All the individual strings within a cell are comma delimited if there is more than one string.

First off, your strings are not "comma" delimited... they are "comma space" delimited. It is not clear how you are trying to do this search... using the Excel Find dialog or via VBA code. Since you are using the word "string" when you talk about text, I'll assume VBA code. The way to do the search you want is use the InStr function and to put the delimiter on each side of the string you are searching in and the string you are searching for (that covers the string you are searching for when it is at the beginning or end of the text. Something like this (using assumed variable names)...

Delim = ", "
If InStr(Delim & CellValue & Delim, Delim & TextToFind & "Delim") Then

I am a little concerned about that trailing comma you show for the C1 value... if that can be there or not, then you need to test for it and remove it from the contents of the CellValue variable before using that variable in the Instr function call I show above.

 

[Marcelo Branco]

Maybe using a formula...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Search​

[/td][td][/td][td]

AAB1,​

[/td][td]

Not Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

AB1​

[/td][td][/td][td]

AB1, B1, C1​

[/td][td]

Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td]

D2, E3​

[/td][td]

Not Found​

[/td][/tr]
[/table]

Formula in D1 copied down
=IF(ISNUMBER(SEARCH(","&A$2&",",","&SUBSTITUTE(C1," ","")&",")),"Found","Not Found")

M.

 

[Rick Rothstein]

=IF(ISNUMBER(SEARCH(","&A$2&",",","&SUBSTITUTE(C1," ","")&",")),"Found","Not Found")

While I still think the OP is looking for a VBA solution (I could be wrong on that, of course), I like your idea of removing all spaces so that a comma can be used for the delimiter thus eliminating the problem of intermittent trailing commas.

 

[Marcelo Branco]

While I still think the OP is looking for a VBA solution (I could be wrong on that, of course), I like your idea of removing all spaces so that a comma can be used for the delimiter thus eliminating the problem of intermittent trailing commas.

I always use this technique to avoid unwanted spaces that could cause problems in comparisons.

M.

 

[noclass1980]

Thanks for the suggestions. I found this solution on another forum that works perfectly

Dim rngX as Range
Set rngX = Worksheets("Source").Range("C1:C10000").Find(StrToFind, lookat:=1)
                ActiveRow = rngX.Row

So I can find the required string (AB1) and get the row number of the first instance

 

[pgc01]

Thanks for the suggestions. I found this solution on another forum that works perfectly

Dim rngX as Range
Set rngX = Worksheets("Source").Range("C1:C10000").Find(StrToFind, lookat:=1)
                ActiveRow = rngX.Row

So I can find the required string (AB1) and get the row number of the first instance

Hi

I find it strange that it works.

... lookat:=1

means that you are looking at the whole value of the cell, not part.

Did you test?

 

[Marcelo Branco]

Some thoughts (besides pgc01's remark)
1. this code fails if the first string is in C1 because the Find method begins the search after C1 in this case (unless there is a header in C1, but this is not clear).
2. take a look at Forum Rules about cross postings (Rule #13 )
Forum Rules

M.