Cumbersome formula

[Excellis]

I have created an embedded IF formula to take care of several possible but slightly different results, the formula works but it looks clumsy & cumbersome and my not even be the right way to do it. If any one can help tidy it up I'd be extremely grateful. I've tried to anonymise my example by using similar data to the actual data.


A typical shop code might be 014 Newtown or 626 Oldtown

=IF(LEFT(H11,3)*1>144,"sto"&LEFT(H11,3)*1&"x@myshop.com",IF(LEFT(H11,3)*1<10,"stor00"&LEFT(H11,3)*1&"x@myshop.com",IF(LEFT(H11,3)*1>99,"stor"&LEFT(H11,3)*1&"x@myshop.com","stor"&0&LEFT(H11,3)*1&"x@myshop.com")))

A typical shop code might be 014 Newtown or 626 Oldtown

What I'm trying to achieve is this:


Any shop that has a reference code 001 to 009 I want to see (example) stor004x@myshop.com
Any shop that has a reference code 010 to 099 I want to see (example) stor064x@myshop.com
Any shop that has a reference code 100 to 144 I want to see (example) stor110x@myshop.com
Any shop that has a reference code >144 I want to see (example) sto540@myshop.com

I'm sorry but I don't know how to attach an example spreadsheet.

Thank you in anticipation

Tom

 

[Legacy 143009]

Thank you for your suggestion. I did consider using something similar but this is a weekly report and it would mean recreating or copy/pasting the table weekly whereas I can 'hide' the formula in the report and it's just there to paste into every line. Thanks again.

Thanks for your consideration but it is totally my fault. My suggestion doesn't meet your needs.