# removing text from cell and calculating in formula

Options

i want to remove the text "months" from its numerical adjacent number and then calculate it against billable hours.

ex #1: month (remove "months from cell") *rate of pay*alloted hours/year

i used this formula, but i get #invalid value in return =VALUE(LEFT(DURATION@row, -6)) * [HOURLY BILL RATE (USD)]@row * (2080 / 12)

however, if i use this formula it works =VALUE(LEFT(DURATION@row, 2)) * [HOURLY BILL RATE (USD)]@row * (2080 / 12) it calculates for a 2 digit month (ex: 11), but not a one digit month (ex: 4). Note, months selection is from a form selected by the person filling it out.

Options

We can add an IFERROR statement around your VALUE() portion of the formula, so when it errors with 2 digit months (meaning there's only 1 digit), we tell it just to grab the 1 character on the left instead of 2.

Try this:

=IFERROR(VALUE(LEFT(DURATION@row, 2)), VALUE(LEFT(DURATION@row, 1))) * [HOURLY BILL RATE (USD)]@row * (2080 / 12)

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Options

We can add an IFERROR statement around your VALUE() portion of the formula, so when it errors with 2 digit months (meaning there's only 1 digit), we tell it just to grab the 1 character on the left instead of 2.

Try this:

=IFERROR(VALUE(LEFT(DURATION@row, 2)), VALUE(LEFT(DURATION@row, 1))) * [HOURLY BILL RATE (USD)]@row * (2080 / 12)

Let me know if that makes sense and works for you!

Cheers,

Genevieve

• Options

Thank you so much for your help, it worked perfectly.

Cheers,

Lucy

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!