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