0

I have a sharepoint list that has $$ data in one of the columns (call it "Opportunity") that can take the form of a distinct dollar value (e.g. $50,000), or a range (e.g. $100,000 - $250,000). I need to create two new columns from this to make a low value and a high value. It needs to be able to copy over a distinct number, or take the low number from the range for the low column, and the high value from the range for the high value column. If it helps illustrate here would be an example:

1) original value: $50,000, calculated low value column: $50,000, calculated high value column: $50,000

2) original value: $100,000 - $250,000, calculated low value column: $100,000, calculated high value column: $250,000

In excel I achieved this with len formulas and lookups (albeit crudely), but am struggling to figure out how to do this in the sharepoint calculated columns.

Thank you for any help!

Kevin

2

This should do what you need:

First calculated column:

=IF( ISERR( FIND("-",Opportunity) ),
      "" & Opportunity,
      TRIM( MID( Opportunity, 1, FIND("-",Opportunity)-1 ) ) )

Second calculated column:

=IF( ISERR( FIND("-",Opportunity) ),
      "" & Opportunity,
      TRIM( MID( Opportunity, FIND("-",Opportunity)+1, 999) ) )

The TRIM is in case there are spaces around the dash. The ( "" & ) is prevent the display of a zero when Opportunity is blank.

enter image description here

| improve this answer | |
0

You should be able to figure it out from this post using left and right from the "-".

Using calculated field to retrieve substring of another field

| improve this answer | |
0

Low value calculated formula:

=IF(ISERR(FIND("-",Opportunity)),Opportunity,LEFT(Opportunity,INT(FIND("-",Opportunity)-1)))

High value calculated formula:

=IF(ISERR(FIND("-",Opportunity)),Opportunity,RIGHT(Opportunity,LEN(Opportunity)-INT(FIND("-",Opportunity))))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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