This is my first StackOverflow question, so apologies if I am unclear.

Currently, my work uses an Excel tracking doc to log project info. The column info is like so:

CELL B1 (Project Number) =IF(B2=""," ",MID(B2,FIND("P2",B2),9))

CELL B2 (Project Name) Client / P2XXXXXXX / Name

Thus, the P2XXXXXXX gets pulled out of B2 and populated into B1.

However, management has recently switched systems, so now, some project numbers have the P2XXXXXXX format and others have a PRJ-XXXXX format.

So we need a formula the produces nothing if the cell is blank and EITHER the P2XXXXXXX number or PRJ-XXXXX number if the cell is not blank. Is it possible? If any further details are needed, let me know. Thanks in advance!

CodePudding user response：

Well, if the / is always there then this can work:

IF(B2="","",MID(B2,FIND("/",B2,1) 2,9))

assuming the name is always 9 characters.

CodePudding user response：

String Between Two Same Characters

Maybe the next month your company will start using a different first letter or could add more numbers e.g. SPRXXXXXXXXXX. So you could solve this problem by extracting whatever is between those two slashes.

=IF(B2="","",TRIM(MID(B2,FIND("/",B2) 1,FIND("/",B2,FIND("/",B2) 1)-FIND("/",B2)-1)))

• Find the first character =FIND("/",B2), but we need the next one:

  =FIND("/",B2) 1
  

• Find the second character but search from the postition after the first found:

  =FIND("/",B2,FIND("/",B2) 1)
  

• Now get the string between them:

  =MID(B2,FIND("/",B2) 1,FIND("/",B2,FIND("/",B2) 1)-FIND("/",B2)-1)
  

  (note how the last minus was 'converted' from a plus to a minus (- = -)).

• Remove the leading and trailing spaces:

  =TRIM(MID(B2,FIND("/",B2) 1,FIND("/",B2,FIND("/",B2) 1)-FIND("/",B2)-1))
  

• Add the condition when the cell is blank:

  =IF(B2="","",TRIM(MID(B2,FIND("/",B2) 1,FIND("/",B2,FIND("/",B2) 1)-FIND("/",B2)-1)))
  

Here's another way using LEFT and RIGHT:

=IF(B2="","",TRIM(LEFT(RIGHT(B2,LEN(B2)-FIND("/",B2)),FIND("/",B2))))