Separating names from numbers and searching for key words - help

**percyth1** · 03-27-2012, 11:57 PM

I need to be able to create a report that tracks the total outstanding sales for each employee in the business minus any outstandings they have fixed. The database that I extract this information from spits it out in a very difficult format, as you can see in the attachment.

The name and total figure is in the same cell and reads like this:
Employee B (4 Outstanding)

I need to be able to separate the figure into another cell with corresponding name.

I also need to determine if any of the outstandings have been fixed this is done by searching the comments and either finding the word "fix" or having a URL at the start of the comments (not at the end). These fixes need to be deducted from the total figure under the corresponding name.

If anyone can help I would greatly appreciate it.

**JBeaucaire** · 03-28-2012, 12:02 AM

First part... in B3 and copied down:

=MID(INDEX(G:G, MATCH(A3&"*",G:G, 0)), LEN(A3)+3,2)+0

**JBeaucaire** · 03-28-2012, 12:22 AM

Second part, a monster... in C3, then copied down:

=CEILING(SUMPRODUCT(((LEFT(OFFSET(INDEX(G:G,MATCH($A3&"*",G:G,0)),2,3,$B3,),4)="http")+ISNUMBER(SEARCH("fix",OFFSET(INDEX(G:G,MATCH($A3&"*",G:G,0)),2,3,$B3,))))/2),1)