SharePoint Calculated Field or Workflow to Substring

Check this formula:

=IF(ISERR(FIND("NETC-";[Subject]));"";MID([Subject];FIND("NETC-";[Subject]);FIND(" ";[Subject]&" ";FIND("NETC-";[Subject]))-FIND("NETC-";[Subject])))

Select allOpen in new window

is that a calculated field style in SP2010?

I had a slight change

Lets say the contents of the field Opportunity Name is
 AGR NETC-5000-2016-NG2580 XXXX MPTE

 I would need to find a way to get NETC-5000-2016-NG2580 into its own variable.  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])))

Which gave me an error

maybe what would make it easier is the substring would start where -NG begins plus one and always go for 6 characters

Using the following:

=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!

Check this:

=MID([Opportunity Name];INT(FIND("-NG";[Opportunity Name]));FIND(" ";[Opportunity Name]&" ";FIND("-NG";[Opportunity Name]))-FIND("-NG";[Opportunity Name]))

when I put

=MID([Opportunity Name];INT(FIND("-NG";[Opportunity Name]));FIND(" ";[Opportunity Name]&" ";FIND("-NG";[Opportunity Name]))-FIND("-NG";[Opportunity Name]))

In the calculated field I get an error

The formula contains a syntax error or is not supported.

Replace ; with ,

Getting some weird results

Opportunity Name=PMNMCI-5000-2015-NG1270 - Tier 3 Field Service Support
XID=-NG1270 should be just NG1270

Opportunity Name=PMNMCI-5000-2015-NG0590-MOD3 ORT HBSS 4.6 Upgrade Mod 3
XID=-NG0590-MOD3  should be just NG0590

Opportunity Name=MPTE Tier 1 Managed Print Services
XID=#VALUE!  should be just "BLANK"

=MID([Opportunity Name];INT(FIND("-NG";[Opportunity Name]));FIND(" ";[Opportunity Name]&" ";FIND("-NG";[Opportunity Name]))-FIND("-NG";[Opportunity Name]))

Did not solve the issue, gave unexpected results see below

 Opportunity Name=PMNMCI-5000-2015-NG1270 - Tier 3 Field Service Support
 XID=-NG1270 should be just NG1270

 Opportunity Name=PMNMCI-5000-2015-NG0590-MOD3 ORT HBSS 4.6 Upgrade Mod 3
 XID=-NG0590-MOD3  should be just NG0590

 Opportunity Name=MPTE Tier 1 Managed Print Services
 XID=#VALUE!  should be just "BLANK"

The first parameter of MID() function is the source string. Second parameter is the starting position in the source string. When you look for a string starting with a dash and you do not want to copy the dash then simply increase the starting position by one.

If you do not want to have #VALUE error messages if starting position is invalied because your search string is missing then use my check in my first version.

And best is when you do that all alone and learn by doing ;- )

Or do you need more explanation?

Oh, the third parameter in MID() function is length of extracted string. If you want to search for single words delimeted by space OR dash char then you need to find the separator by checking twice and choosing the first match.

Like this:

=IF(ISERR(FIND("-NG";[Opportunity Name]));"";MID([Opportunity Name];FIND("-NG";[Opportunity Name])+1;MINA(FIND(" ";[Opportunity Name]&" ";FIND("-NG";[Opportunity Name]))-1;FIND("-";[Opportunity Name]&"-";FIND("-NG";[Opportunity Name])+1)-1))))

Here the link to list of available functions:

https://sharepointdiva.wordpress.com/2012/09/06/text-formulas-for-the-calculated-column/

My last version has missmatch in closing braces.

Here the corrected and extended version:


=IF(ISERR(FIND("-NG";[Opportunity Name]));"";MID([Opportunity Name];FIND("-NG";[Opportunity Name])+1;MINA(FIND(" ";[Opportunity Name]&" ";FIND("-NG";[Opportunity Name]))-FIND("-NG";[Opportunity Name])-1;FIND("-";[Opportunity Name]&"-";FIND("-NG";[Opportunity Name])+1)-FIND("-NG";[Opportunity Name])-1)))

AWESOME!!!!

One last question...

Is there a way in the formula when you look at -NG that the next column -NGX  X has to be numeric???

AWESOME.... Thanks for the help!!!!!

Thank you for grading : )

Can you please open a new Question and post the examples and expected results?