Pulling out 1/2 char strings into other columns.

[Jabbitar]

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]

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]

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]

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]

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]

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

Please Login or Register  to view this content.

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.

[Jabbitar]

Ok, a really weird thing has just happened, it has found all of the positions correctly. The only thing is, it won't find the C position if it is listed after another one, however if it is by itself it is listed there...

Jarrett Jack, Ind PG PG
Larry Hughes, NY SG SG
Gerald Wallace, Cha SF, PF SF PF
Kevin Love, Min PF PF
Marc Gasol, Mem C C
D.J. Augustin, Cha PG PG
Peja Stojakovic, Nor SF SF
Chris Duhon, NY PG PG
Andre Iguodala, Phi SG, SF SG SF
Raymond Felton, Cha PG PG
Danny Granger, Ind SF SF
Manu Ginobili, SA SG SG
Andrea Bargnani, Tor SF, PF, C SF PF
Anderson Varejao, Cle PF, C PF
Ronny Turiaf, GS PF, C PF
Steve Blake, Por PG PG
Marquis Daniels, Ind SG, SF SG SF

I'm hoping it comes out correctly, just did a quick copy paste/

[Jabbitar]

Ok, that didn't show as well as I'd planned, so here is the file!

Its on the first worksheet.

[NBVC]

Not sure what you mean... seems to have worked for me. See attached and let me know what, if anything, is wrong.

[NBVC]

Ok,

I see from your attachment what you mean... it doesn't pull the "C" if it is the last listed item in the cell.. that is because we were really looking for "C " so I adjust formula to add a space at end of B2 to check for "C ".

Now formula in C2 is:

Please Login or Register  to view this content.

CSE entered and copied across and down...

See your attached file, fixed.

[Jabbitar]

EDIT: Perfect! Thank you SO much!

Now onto other problems! :D

[NBVC]

I see it on your sheet correctly, but when I c/p it into mine (making sure to press Ctl/shift/Enter to get the {}, it still wont show the C. I'm on 2003 Excel, will that effect it?

See my last post... I got your attachment after I sent my sample...and I fixed your file.