Help creating calculated column(s) to read, then split or pass on data

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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