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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also is that last example correct or should it be stor540x@myshop.com

 

[Legacy 143009]

Your formula seems to be ok like this. If you want a short hand, make a simple table like this in another sheet. Let say Sheet2:

Column A	Column B	Column C
001	009	stor004x@myshop.com
010	099	stor064x@myshop.com
100	144	stor110x@myshop.com
145		sto540@myshop.com

Your formula can be simple as this:

=VLOOKUP(LEFT(H11,3), 'Sheet2'!$A$1:$C$4, 3, 1)

 

[Fluff]

If the last example is wrong, how about

="stor"&LEFT(H11,3)&"x@myhop.com"

 

[Excellis]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also is that last example correct or should it be stor540x@myshop.com

Thank you, I'll do that. I'm using Excel 2016, and my example sto540 is correct. Any shops over number 144 drop the 'x' and the 'r' in the email address.

 

[Legacy 143009]

If the last example is wrong, how about

="stor"&LEFT(H11,3)&"x@myhop.com"

Oh, again I totally got wrong. Discard my solution.

 

[Fluff]

Ok how about

="stor"&LEFT(H11,3)&IF(LEFT(H11,3)+0>144,"","x")&"@myshop.com"

 

[Excellis]

Ok how about

="stor"&LEFT(H11,3)&IF(LEFT(H11,3)+0>144,"","x")&"@myshop.com"

Ok how about

="stor"&LEFT(H11,3)&IF(LEFT(H11,3)+0>144,"","x")&"@myshop.com"

Fantastic. With a very minor modification that works perfectly. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Excellis]

Your formula seems to be ok like this. If you want a short hand, make a simple table like this in another sheet. Let say Sheet2:

Column A	Column B	Column C
001	009	stor004x@myshop.com
010	099	stor064x@myshop.com
100	144	stor110x@myshop.com
145		sto540@myshop.com

Your formula can be simple as this:

=VLOOKUP(LEFT(H11,3), 'Sheet2'!$A$1:$C$4, 3, 1)

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.