Pulling out 1/2 char strings into other columns.

**Jabbitar** · 03-02-2009, 10:16 PM

Hi all,

I'm new to this, and trying to get a spreadsheet organised for my nba fantasy team. To save time, (and me having to enter the positions of 300ish people individually) I'm trying to pull out their positions into seperate colums.

The data I am trying to pull is from these:

Jarrett Jack, Ind PG
Gerald Wallace, Cha SF, PF

=IF(FIND("PG",$B2),"PG","")

That is the formula I am trying to use, it finds the PG for some, but when it doesn't it gives me this "#VALUE!" in the cells, instead of a blank as I have tried to input.

Now, there are 5 combinations that I am trying to pull out: PG, SG, SF, PF and C.

There are multiple combinations that need to be pulled out, and placed into my 3 cells allocated (EG - 1 for Pg, 1 for SG, 1 for SF).

I have also found a flaw that takes the C from Cha (being the team) instead of from the position. Can I search for a individual C? I've tried to google these things, but I can't find an answer.

If you need more information, tell me how to put my sheet on here to show you, I would appreciate any help you can give me.

Thanks.

**NBVC** · 03-02-2009, 10:26 PM

You need to include the ISNUMBER() function. FIND() returns position number and so ISNUMBER() checks to see if a "number" is found... and if yes, returns TRUE

=IF(ISNUMBER(FIND("PG",$B2)),"PG","")

For finding a lone "C" add a space afterwards

e.g.

=IF(ISNUMBER(FIND("C ",$B2)),"C","")

**Jabbitar** · 03-02-2009, 10:33 PM

Awesome! Thank you!

Another question added onto that, can I nest these If functions together to include PG, SG, SF and C? Or is that too complex for Excel to do? I know that in their listings, they are always in the above order, if that helps?

**NBVC** · 03-02-2009, 10:40 PM

Will there only be 1 match per cell or multiple?

If 1 then, nested IF or similar would work.. if multiple, then it becomes more complex.

**Jabbitar** · 03-02-2009, 10:45 PM

There could be 1, or there could be multiple, depending on the player.

=IF(ISNUMBER(FIND("PG",$B2)),"PG",""),=IF(ISNUMBER(FIND("SG",$B2)),"SG",""),=IF(ISNUMBER(FIND("SF",$B2)),"SF",""),=IF(ISNUMBER(FIND("PF",$B2)),"PF",""),=IF(ISNUMBER(FIND("C ",$B2)),"C","")

I did a quick Copy Paste and got "FALSE" in the Cell it was meant to display the position.

So, for instance:
Gerald Wallace, Cha SF, PF - Would have one cell with SF, and the second (to the right) with PF.

Andrea Bargnani, Tor SF, PF, C - Would have all 3 cells filled. I haven't seen anything larger than a 3 position combination, so I'm going for 3 cells.

If it is too complex, I can make 5 cells and search by position, but I'd like to somehow nest them, if possible.

Thank you for all your help, its greatly appreciated!

EDIT: I read that post wrong, there would be 1 match per cell, but a possibility of more than 1 match per player.

**NBVC** · 03-02-2009, 11:01 PM

Assuming those are the only 5 options.. then try this in a cell within same same row as B2 (i.e. Row 2).:

=IF(COLUMNS($A$1:A$1)>SUMPRODUCT(--ISNUMBER(SEARCH({"PG","SF","SG","C ","PF"},$B2))),"",INDEX({"PG","SF","SG","C","PF"},SMALL(IF(ISNUMBER(SEARCH({"PG","SF","SG","C ","PF"},$B2)),{1,2,3,4,5}),COLUMN(A$1))))

Once entered confirm it with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear around the formula.

Then copy it over 5 cells and down as far as you need.

Only the matches should appear in those groups of 5 cells.

Note: If you have more than 5 possibilities, it would be easier to replace those hard-coded values with a range containing the possibilities.