Excel formula that produces one of two options

user_18412536 0
Viewed 66

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!

excel-formula excel
3 Answers

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.

user_49617000

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))))
user_98140690

Although you can solve this problem with a combination of slicing, trimming, and complex conditionals, the most expressive and easy to maintain solution is to use regular expressions. Regular expressions have a bit of a learning curve, but there's a great playground website where you can experiment with them, and this page has a pretty good writeup on how regular expressions work in excel.

Specifically, this regular expression addresses the two naming conventions you've highlighted, but it can be updated to support more naming conventions as your company inevitably adds more:

P(RJ-)?((\d){9}|(\d){5})

To break that down from left to right:

P: both patterns start with a "P"

(RJ-)? One pattern follows with "RJ-", but the other doesn't. This is a grouped part of the pattern, and the question mark means that this part of the pattern is optional.

((\d){9}|(\d){5}): by far the nastiest part, but this basically means that there is going to be a sequence of numbers (\d), and there will either be nine of them or five of them. By wrapping the whole thing in parenthesis, they are always the second captured group, no matter the length of the sequence of numbers. This means that you can always extract the project id by looking at the value of the second capture group.

You can also make the expression more generalized by replacing ((\d){9}|(\d){5}) with simply (\d+). That just means "one or more digits." That gives you a much more simplified overall expression of this:

P(RJ-)?(\d+)

Depending on whether or not you care about validating strictly that project ids are 5 OR 9 digits long, that pattern above might be suitable, and it has the benefit of being more flexible. Still, the project ID is in the second captured group.

user_132625360
Related