0

I need to find a way to get a portion from a field
called opportunity name into its own field called XID,

an example of an opportunity name is NETC-5000-2016-NG2580.

Start and end locations will not always be the same but the beginning would be -NG and the end would be the first space after that.

So in the case above NG2580

Not all Opportunity Names would have a -NG

And tried

=IF(
    ISERR(
          FIND( "-NG" ; [Opportunity Name] )
         )
    ;""
    ;MID(
         [Opportunity Name]
         ;FIND( "-NG" ; [Opportunity Name] )
         ;FIND(
               " "
               ;[Opportunity Name]&" "
               ;FIND( "-NG" ; [Opportunity Name] )
              )
               -FIND( "-NG" ; [Opportunity Name] )
        )
    )
3
  • I tried to be careful with adding spaces and linebreaks, did I delete that ) after the FIND that looks for the space " "? Or is that the error you have in your Formula? Commented Jan 10, 2017 at 9:57
  • it just got a generic error when I tried to add to the calculated field and clicked ok... maybe what would make it easier is the substring would start where -NG begins plus one and always go for 6 characters
    – user63270
    Commented Jan 10, 2017 at 10:00
  • Yea tried this and get an error =IF(ISERR(FIND( "-NG" ; [Opportunity Name] ));"";MID([Opportunity Name];FIND( "-NG" ; [Opportunity Name] );FIND(" ";[Opportunity Name]&" ";FIND( "-NG" ; [Opportunity Name] ));FIND( "-NG" ; [Opportunity Name] ) ) ) The formula contains a syntax error or is not supported.
    – user63270
    Commented Jan 10, 2017 at 10:16

1 Answer 1

0

Try this formula

=IF(ISERROR(MID(A4,SEARCH("-NG",A4,1)+1,SEARCH(" ",A4,SEARCH("-NG",A4))-SEARCH("-NG",A4)-1)),"",MID(A4,SEARCH("-NG",A4,1)+1,SEARCH(" ",A4,SEARCH("-NG",A4))-SEARCH("-NG",A4)-1))

Replace A4 by SharePoint column name - [Opportunity Name]

3
  • 1
    note: if you add a Tab or 4 spaces, Formulas in posts will be formatted as code and syntax highlighting will be applied Commented Jan 10, 2017 at 10:53
  • =RIGHT([Opportunity Name],LEN([Opportunity Name])-INT(FIND("-NG",[Opportunity Name]))) Opportunity Name = PMNMCI-5000-2015-NG1270 - Tier 3 Field Service Support Result is - NG1270 - Tier 3 Field Service Support Now I need to figure out how to get on the 6 characters NG1270 AND when -NG does not exist I don't want #VALUE!
    – user63270
    Commented Jan 10, 2017 at 12:14
  • Try above updated formula Commented Jan 11, 2017 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.