INDEX the cell after the "/"

[William Benton]

I have a formula that returns what is in a certain cell in column B.

I want the formula to see if there is a "/" in the cell (column B) and if so check for "/" and return what is to the right of "/" and if not return what is in the cell.

This one catches the first "I" - the formula is:

=IF(COUNTIF(SCHEDULE!$F$5:$F$29,"I")>=ROWS($1:1),INDEX(SCHEDULE!B9:B29,SMALL(IF(SCHEDULE!$F$5:$F$29="I",ROW($1:$25)),ROWS($1:1))),"")

If this could be reduced I am all ears.

I don't see if I can attach a sample anywhere.

Bill

 

[MARK858]

I don't see if I can attach a sample anywhere.

Please see the link below

http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[FDibbins]

A bit confused here :?

Your request says to test for / but your formula is looking for I? So which 1 do you want?
Also, doing the formula that way, you need to keep the ranges the same size...\
=IF(COUNTIF(SCHEDULE!$F$5:$F$29,"I")>=ROWS($1:1),INDEX(SCHEDULE!B9:B29,SMALL(IF(SCHEDULE!$F$5:$F$29="I",ROW($1:$25)),ROWS($1:1))),"")

I would modify that to this...
=IFERROR(IDEX(SCHEDULE!$B:$B,SMALL(IF(SCHEDULE!$F$5:$F$29="I",ROW($F$5:$F$25)),ROWS($1:1))),"")

 

[William Benton]

A bit confused here :?

Your request says to test for / but your formula is looking for I? So which 1 do you want?
Also, doing the formula that way, you need to keep the ranges the same size...\
=IF(COUNTIF(SCHEDULE!$F$5:$F$29,"I")>=ROWS($1:1),INDEX(SCHEDULE!B9:B29,SMALL(IF(SCHEDULE!$F$5:$F$29="I",ROW($1:$25)),ROWS($1:1))),"")

I would modify that to this...
=IFERROR(IDEX(SCHEDULE!$B:$B,SMALL(IF(SCHEDULE!$F$5:$F$29="I",ROW($F$5:$F$25)),ROWS($1:1))),"")

Thankyou!

 

[Rick Rothstein]

I have a formula that returns what is in a certain cell in column B.

I want the formula to see if there is a "/" in the cell (column B) and if so check for "/" and return what is to the right of "/" and if not return what is in the cell.

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE("/"&B2,"/",REPT(" ",400)),400),400))

 

[William Benton]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE("/"&B2,"/",REPT(" ",400)),400),400))

Thank you Rick!

 

[William Benton]

Thankyou!

I look for the "I" to return what is in column B. Trouble is what is in B could either be one name or two names separated by "/". If separated by the "/" I want to return what is to the right of it and if no "/" just return the name.

Sorry for the late reply. Thanks for helping.

 

[FDibbins]

Could you give some samples?