Need suggestions on ‘Find’ function variation

DLB20720 · May 11, 2014

I have a worksheet that reads paragraphs from a live feed that I import into Excel. I have successfully written quite a few functions to parse the info but am struggling to find a function for one facet of the info I receive and am hoping someone may be able to offer a suggestion.

The info I import is regarding stock upgrades from various analysts. Part of the information I receive has the name of the analyst or company that issued the ratings change. I have ratings on the analysts themselves and am looking to ‘score’ them in Excel but have not been able to write a function to isolate the name in a manner where I can use it in an Index / Match function. I have ratings on 150 analysts and some names consist of 1 word (4 characters) and some have 3 words with commas and ampersands included. The longest name has 30 characters. One of my biggest problems is that I have several names that begin with the same word (Williams) so I need to include the 2^nd word to make the value unique to be able to identify it with Index / Match. I’ll list 4 values that represent some of the extremes I am dealing with.

HSBC
Keefe, Bruyette & Woods
Williams Capital
Williams Financial Group

So far, I have successfully written a Right / Find function that lists the analysts name in a cell beginning with the first word, but also includes the first 30 characters. I just can’t find a function to isolate enough of the name to make it unique enough for Index / Mach to work. Hopefully someone can offer a suggestion. Thanks!

skywriter · May 11, 2014

Can you post a screen shot with some sample data showing an example of the names giving you trouble and what you would like the ideal outcome to look like?

DLB20720 · May 11, 2014

Skywriter, it is not so much the names giving me trouble as it is the variations in those names. I have attached a screenshot but I’m not sure if this will help. The screenshot shows how far I have made it with my formulas, but as I indicated before, I can’t narrow this down to the variation in the names.

I’ve tried variations of a LEN function to return anywhere from 4 to maybe 1o characters with the hope it would be accurate enough for Index / Match to read but I still had about 10% of the 150 names that it would not ‘read’ correctly. I’ve tried a number of different formulas but am at a loss as to a way to isolate the actual name.

Thanks for your response.

I can't find a way to insert my screenshot! i thought I had done that before but can't find anything now.

skywriter · May 11, 2014

Here's what the screen shot would look like. It would be a huge help because we could see your data.
If you have to copy some of your data to another sheet change anything that might be sensitive but make sure it's similar to what you have so I can see what's giving you the problem.

Click on the Jeanie link below the screen shot to get the software. It's easy to use, install the add in, highlight the range you want to screen shot, go to add in click the Excel Jeanie Html in the menu click it, you will see the range
you highlighted, click Forum Standard near the bottom, the code will be copied to the clipboard, come back here hit reply to my post, and paste the code in. The borders showing column letters and row numbers are all automatic as well as showing us the formulas you are using. Hope this helps.

Excel Workbook

A

B

C

1

2

4

Sheet5

DLB20720 · May 12, 2014

Skywriter, I attempted to download the Jeanie but had a security warning pop up and I can’t take the chance of allowing that on my computer. I use this to make $$.

I have manually entered info as it appears in my spreadsheet. I left column A blank but it contains the text targeted by the formula in Column B. The formula I am using is displayed in B2 with examples that are returned displayed in B3 through B7. These 5 examples pretty much show the extremes of the results returned and what I am trying to target. In the formula, I chose to return 30 characters because that represents the largest number of characters in my list of analysts’ names. The data returned in column B represents my best efforts so far to isolate and the data from the original text. The data in column D is what I would like to isolate from the data returned in column B. Hopefully that makes sense.

As you can see, the names in column D vary from 4 characters in length to 30 characters. Some names have commas & ampersands.

A

B

C

D

Original Text

Formula used / text returned

?

Desired end result

=LEFT(A1,30)

Keefe, Bruyette & Woods from $

Keefe, Bruyette & Woods

HSBC. They now have a $20 pric

HSBC

Williams Capital from $18.50 t

Williams Capital

Williams Financial Group. They

Williams Financial Group

Burrill Institutional Research

Burrill Institutional Research

I have tried everything I can think of or locate on the internet, but I haven’t found anything yet that will isolate the analysts names to a point where I can use Index / Match against the 150 names in my list. I am at a loss.

I appreciate your input.

Peter_SSs · May 12, 2014

It seems from your attempted formula, the the analyst name would be the first thing found in the column A cell (check my row 9 result to see if it is what you want in this regard).
Anyway, see if this helps.

I have my list of analysts in E2:E6. See if you can modify this formula to suit your needs.

Excel Workbook

A

B

C

D

E

2

Williams Financial Group. They blah blah

Williams Financial Group

Keefe, Bruyette & Woods

3

HSBC. They now have a $20 pric blah blah

HSBC

4

Burrill Institutional Research blah blah

Burrill Institutional Research

Williams Capital

5

No analyst found

Williams Financial Group

6

Keefe, Bruyette & Woods from $ blah blah

Keefe, Bruyette & Woods

Burrill Institutional Research

7

Something else blah blah

No analyst found

8

Williams Capital from $18.50 t blah blah

Williams Capital

9

Other text Williams Capital from $18.50 t blah blah

No analyst found

Extract Analyst

MickG · May 12, 2014

If you have a list of the company names and they are of a standard format, you could try this:-

Code:

Function nFd(rng As Range) As String
Dim Ray
Dim n As Long
Ray = Array("HSBC", "Keefe, Bruyette & Woods", "Williams Capital", "Williams Financial Group", "Burrill Institutional Research")
For n = 0 To UBound(Ray)
    If InStr(rng, Ray(n)) > 0 Then nFd = Ray(n): Exit For
Next n
End Function

DLB20720 · May 12, 2014

Peter, the formula you offered works perfectly. And as I entered the formula I could see what the formula was targeting so I understand it perfectly (that may be a first!). Thank you, that ends about 20 hours of fruitless work on this problem. I had tried some type of SEARCH function before, but I know I didn’t use anything close to the one you presented. Thank you, I appreciate your response.

MickG, I do have a list of company names and they are normally presented in the same format, but occasionally I do find variations. As an example, I sometimes see ‘Bank of America’ but sometimes it is presented as ‘BofA’. I have 150 names in the array but it is no big deal to add more names or variations as I find them. My biggest problem with what you have offered is my lack of knowledge of VBA. There are people who really suck at using VBA, and then there is me! I’m trying to learn VBA and I’m not sure why I struggle with it so much, but I can see how useful it is and therefore the need for me to learn more about it. I’m going to try your suggestion, but I’m sure I’ll manage to mess something up with it. I appreciate your suggestion though.

Peter_SSs · May 12, 2014

DLB20720 said:
Peter, the formula you offered works perfectly. And as I entered the formula I could see what the formula was targeting so I understand it perfectly (that may be a first!). Thank you, that ends about 20 hours of fruitless work on this problem. I had tried some type of SEARCH function before, but I know I didn’t use anything close to the one you presented. Thank you, I appreciate your response.

That's great, and it's what the forum is for

BTW, am I correct in thinking that you are composing your posts in Word before transferring them to the forum?
If so, why do you do it that way?
You may not be aware that it is generating a whole lot of extra code in your post. Try clicking "Reply With Quote" at the bottom right of one of your own posts and look at the Reply window. (You can subsequently click "Cancel" to get out of that reply)

DLB20720 · May 12, 2014

Peter, I have learned more about Excel from this forum than any other venue, including the Microsoft website.

I am using Word and cut and paste into MrExcel. I was not aware that it adds additional code and I'm amazed you are somehow able to see that. I did click on 'Reply With Quote' but I can't see the code. Using Word is probably an old habit and originates from the days prior to having Spell Check available for online text entry. I'll aviod using Word to compose future posts.

Thanks again!

Need suggestions on ‘Find’ function variation

DLB20720

Board Regular

Excel Facts

skywriter

Well-known Member

DLB20720

Board Regular

skywriter

Well-known Member

DLB20720

Board Regular

Peter_SSs

MrExcel MVP, Moderator

MickG

MrExcel MVP

DLB20720

Board Regular

Peter_SSs

MrExcel MVP, Moderator

DLB20720

Board Regular

Similar threads

Forum statistics

Share this page

Need suggestions on ‘Find’ function variation

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP, Moderator

MrExcel MVP

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock