3

I am stuck with this one from hours.

I have range with cells with string values:

A1 text1

A2 text2

An text3

And other column with other string values like:

B1 text1sampletext

B2 text2sampletext

B3 text3sampletext

B4 text1sampletext

B5 text1sampletext

I have to check if text in column A is sub string of text in column B. If it is, to set in column C the text from column A.

Like this:

B1 text1sampletext - C1 text1

B2 text2sampletext - C1 text2

B3 text3sampletext - C1 text3

B4 text1sampletext - C1 text1

B5 text1sampletext - C1 text1

3 Answers 3

6

This should work in Excel 2010 and doesn't require "array entry"

=IFERROR(LOOKUP(2^15,FIND(A$1:A$3,B1),A$1:A$3),"not found")

FIND is case-sensitive, if you don't want the formula to be case-sensitive replace FIND wiith SEARCH

Assumes that A1:A3 doesn't contain blanks

0
1

I didn't manage for now to make Dave (brettdj)'s solution working.

Here is a UDF that would work:

Function copyText(rSubstr As Range, rText As Range)
'Check if any of the cell value from rSubstr exists in rText
Dim c As Range

If rText.Count > 1 Then Exit Function

For Each c In rSubstr
    If InStr(1, rText.Value, c.Value) > 0 Then
        copyText = c.Value
        Exit Function
    End If
Next c
End Function

Simply use this function in your worksheet:

=copyText($A$1:$A$3;B1)
1

No doubt Barry will embarrass me

But something like this in C1 and copied down will work for xl07 and xl10

Press together to enter this as an array formula

=IFERROR(INDEX(A$1:A$3,MATCH(TRUE,NOT(ISERROR(FIND(A$1:A$3,B1))),0)),"not found")

for all xl versions again with Press =IF(NOT(ISNA(MATCH(TRUE,NOT(ISERROR(FIND(A$1:A$3,B1))),0))),INDEX(A$1:A$3,MATCH(TRUE,NOT(ISERROR(FIND(A$1:A$3,B1))),0)),"not found")

4
  • I was not able to use your code. The first give me errors, i try to fix it, but still no working. The second gives me only "not found". Could assist to find the problem?
    – Joro
    Mar 1, 2012 at 10:44
  • the second argument is missing in the first formula in the ISERROR formula inside the MATCH. I am looking for an answer to make it work
    – JMax
    Mar 1, 2012 at 13:32
  • the second formula worked OK for me but 1st formula needs a closing parenthesis to work, suggested edit to that effect Mar 1, 2012 at 15:39
  • @barryhoudini thx, I see you have made the update
    – brettdj
    Mar 1, 2012 at 21:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .