Skip to main content
added 1 character in body
Source Link

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

The ViewMaster365ViewMaster 365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:https://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:https://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

The ViewMaster 365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:https://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

added 5 characters in body
Source Link

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

##CalcMaster Editor II have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

####BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

#The ViewMaster365 way

The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffectssideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/Howhttps://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

##CalcMaster Editor I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

####BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

#The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:https://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

deleted 25 characters in body
Source Link

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See ViewMaster365.com/functionshttps://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

##CalcMaster Editor I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

####BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

#The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See ViewMaster365.com/functions for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

##CalcMaster Editor I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

####BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

#The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

##CalcMaster Editor I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

####BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

#The ViewMaster365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.

added 79 characters in body
Source Link
Loading
Source Link
Loading