Find a string within a column

[Rookie_User]

I have two columns of data and I want to know where column one's data exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

[CLR]

In C1 put this and copy down..........

=IF(COUNTIF(A:A,"*"&B1&"*")>0,"Found in column A is yes","Not found")

Vaya con Dios,
Chuck, CABGx3



"Rookie_User" wrote:

> I have two columns of data and I want to know where column one's data exists
> in column two. It is not as easy as just using a countif function or
> anything. Please see my data set. Column C is just used to see if it
> exists. Please help.
>
> A B C
> Jason Hopson Jason Found in Column A is yes
> Jack Alan Sam Found in Column A is yes
> Mark Bran Noelia Found in Column A is yes
> Noelia Sam Tina Not found
> Sam Zink

[Biff]

That could return false positives:

Eric Sampson............Sam
Alice Markson...........Mark

A little more robust: (but still not 100% bulletproof)

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
")))),"Yes","No")

Biff

"CLR" <[email protected]> wrote in message
news:[email protected]...
> In C1 put this and copy down..........
>
> =IF(COUNTIF(A:A,"*"&B1&"*")>0,"Found in column A is yes","Not found")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Rookie_User" wrote:
>
>> I have two columns of data and I want to know where column one's data
>> exists
>> in column two. It is not as easy as just using a countif function or
>> anything. Please see my data set. Column C is just used to see if it
>> exists. Please help.
>>
>> A B C
>> Jason Hopson Jason Found in Column A is yes
>> Jack Alan Sam Found in Column A is yes
>> Mark Bran Noelia Found in Column A is yes
>> Noelia Sam Tina Not found
>> Sam Zink

[Rookie_User]

WOW, that is so awesome thank you. Now that you got me 95% of the way there
can you maybe make one more modifcation to accomplish the next iteration.
The same situation above, heres the data, I changed it from above but I just
need to do one more iteration to "tune" our numbers. So, in this example

Quotes Column A Order -Column B

Amy Woo ~-060251-a 113028-Herb Mills ICC
Adams Dr Art ~-060229-a 113283-Dr Jason Daaboul ICC
Alan Mills ~-050810-d 113376-Hortman ICC
Allan-Dr. Finney ~-050510-b 113401-HedrickDodson ICC
Abbot ~-060174-a 113422-JVH-Cooper-06 ICC
Advanced Endodontics-060171-a 113466-Lacy ICC
Arrington ~-060152-a 113496-Drs Cross-Wan Burns ICC

Even though colB will not exactly match ColA - if any part of the cell
contents in column b matches any part of the cell contents in column A I need
to know. Is there a way to do this. Additionally, if you have time can you
explain your answer, your formula is awesome you gave me before but I can't
understand it. If it takes too much time don't worry about it. I am trying
to get these results early this moring PST time.
"Biff" wrote:

> That could return false positives:
>
> Eric Sampson............Sam
> Alice Markson...........Mark
>
> A little more robust: (but still not 100% bulletproof)
>
> =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
> ")))),"Yes","No")
>
> Biff
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > In C1 put this and copy down..........
> >
> > =IF(COUNTIF(A:A,"*"&B1&"*")>0,"Found in column A is yes","Not found")
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Rookie_User" wrote:
> >
> >> I have two columns of data and I want to know where column one's data
> >> exists
> >> in column two. It is not as easy as just using a countif function or
> >> anything. Please see my data set. Column C is just used to see if it
> >> exists. Please help.
> >>
> >> A B C
> >> Jason Hopson Jason Found in Column A is yes
> >> Jack Alan Sam Found in Column A is yes
> >> Mark Bran Noelia Found in Column A is yes
> >> Noelia Sam Tina Not found
> >> Sam Zink
>
>
>

[Biff]

>Even though colB will not exactly match ColA - if any part of the cell
>contents in column b matches any part of the cell contents in column A I
>need
>to know. Is there a way to do this.

Not that I know of. You can't be that general, "if any part matches any
part". You have to search for specific substrings.

Based on the last sample you posted I didn't see anything that even closely
matched. (other than DR and I'm guessing that isn't a match)

>can you explain your answer

The formula just searches for entire words that have a space before and
after. That way, Mark and Markson can't be a match.

If the cell contents was:

Mark Adams

There obviously isn't a space before Mark so the formula concatenates a
space to the beginning and to the end of the cells contents.

Biff

"Rookie_User" <[email protected]> wrote in message
news:[email protected]...
> WOW, that is so awesome thank you. Now that you got me 95% of the way
> there
> can you maybe make one more modifcation to accomplish the next iteration.
> The same situation above, heres the data, I changed it from above but I
> just
> need to do one more iteration to "tune" our numbers. So, in this example
>
> Quotes Column A Order -Column B
>
> Amy Woo ~-060251-a 113028-Herb Mills ICC
> Adams Dr Art ~-060229-a 113283-Dr Jason Daaboul ICC
> Alan Mills ~-050810-d 113376-Hortman ICC
> Allan-Dr. Finney ~-050510-b 113401-HedrickDodson ICC
> Abbot ~-060174-a 113422-JVH-Cooper-06 ICC
> Advanced Endodontics-060171-a 113466-Lacy ICC
> Arrington ~-060152-a 113496-Drs Cross-Wan Burns ICC
>
> Even though colB will not exactly match ColA - if any part of the cell
> contents in column b matches any part of the cell contents in column A I
> need
> to know. Is there a way to do this. Additionally, if you have time can
> you
> explain your answer, your formula is awesome you gave me before but I
> can't
> understand it. If it takes too much time don't worry about it. I am
> trying
> to get these results early this moring PST time.
> "Biff" wrote:
>
>> That could return false positives:
>>
>> Eric Sampson............Sam
>> Alice Markson...........Mark
>>
>> A little more robust: (but still not 100% bulletproof)
>>
>> =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
>> ")))),"Yes","No")
>>
>> Biff
>>
>> "CLR" <[email protected]> wrote in message
>> news:[email protected]...
>> > In C1 put this and copy down..........
>> >
>> > =IF(COUNTIF(A:A,"*"&B1&"*")>0,"Found in column A is yes","Not found")
>> >
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> > "Rookie_User" wrote:
>> >
>> >> I have two columns of data and I want to know where column one's data
>> >> exists
>> >> in column two. It is not as easy as just using a countif function or
>> >> anything. Please see my data set. Column C is just used to see if it
>> >> exists. Please help.
>> >>
>> >> A B C
>> >> Jason Hopson Jason Found in Column A is yes
>> >> Jack Alan Sam Found in Column A is yes
>> >> Mark Bran Noelia Found in Column A is yes
>> >> Noelia Sam Tina Not found
>> >> Sam Zink
>>
>>
>>

[ricxl]

This still doesn't do what you really want, I'm trying to do basically the same thing, but here's another partial solution. Perhaps someone might have the answer.


Step 1 - Data in original format: Need to extract the Upper Case names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out of each of the cells:
and put them in to Column2. What is below would be the ideal result, but what I can now do is only one item, "BOB" for exaple, at a time and I would need to copy the result for BOB to a 3rd column and then run the formula again for the next case "****" but before that is done, manually cut & paste BOB to another column then run through the procedure for BOB **** HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still have to cut & paste.

column1 column2 column3 column4
acBOBee BOB rem achieved through =IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt **** rem achieved through =IF(ISNUMBER(SEARCH("****",Col1)),"****","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it with exact matchs, but not when looking at a portion of a string in a larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D

[ricxl]

I posted a similar request and got this reply from Ron Rosenfeld. Try it out, it worked for me and it might be what you're looking for. It does require a freeware add-in. You might also check out my post:
http://www.excelforum.com/showthread.php?t=521909

To pull out any of a list of words from your column 1, you could use the
formula:

=REGEX.MID(A1,"(BOB)|(TOM)|(****)|(HARRY)")

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

To make the list more manageable, you could put the names in a range named rng
(one name per cell) and use the formula:

=REGEX.MID(A1,MCONCAT(rng,"|"))

To use the above formulas, you must download and install Longre's free
morefunc.xll add-in from:

http://xcell05.free.fr