Complicated IF formula (how do I combine 11 if statements)

[alinka]

Hi Excel wizards,

I am a newbie to excel and have a complicated problem. See spreadsheet below for reference, plz.

This is what I need to do:

1) I regularly get a large amount of name data in columns A through C. Each row has an email syntax in Column D. In Column E, I created a formula that writes an email address for the corresponding syntax. I have 11 email syntaxes in total.

2) I need a formula that can read an email syntax in D, and use one of my 11 formulas to create an email address. In other words, I want the formula to do this:

a) IF we find "first.last" in D, use formula =A2&"."&C2&(MID(D2,(FIND("@",D2)),50))

b) IF we find "first_last" in D, use formula =A3&"_"&C3&(MID(D3,(FIND("@",D3)),50))

c) IF we find "first.m.last" in D, use formula =A4&"."&(IF(B4="","",(LEFT(B4,1))&"."))&C4&(MID(D4,(FIND("@",D4)),50))

and so on for the next 8 syntaxes that are shown in D.

So, there are 11 IF statements, that need to be combined into 1 formula. How can I go about doing this??? Or do you envision an easier way? Is it possible to write a vb code that would do this? ANY HELP IS APPRECIATED. THANKS!!!!

email-syntax.xls
	A	B	C	D	E
1	FirstName	Middle	LastName	EmailSyntax	EmailFormula
2	Alan	J	Lacy	first.last@company.com	Alan.Lacy@company.com
3	William	C	White	first_last@company.com	William_White@company.com
4	Michael		Buxton	first.m.last@company.com	Michael.Buxton@company.com
5	Jerome	Be	Karlin	first_m_last@company.com	Jerome_B_Karlin@company.com
6	William	Jack	Rudolphsen	first_m_last@company.com	William_J_Rudolphsen@company.com
7	Mark	A	Wagner	flast@company.com	MWagner@company.com
8	Robert	M	Kral	lastf@company.com	KralR@company.com
9	William	L	Hubbs	fmlast@company.com	WLHubbs@company.com
10	Alan	J	Lacy	firstlast@company.com	AlanLacy@company.com
11	Jerome		Karlinsky	FLast>7@company.com	JKarlins@company.com
12	William		Rudolphsen	FLast>8@company.com	WRudolphs@company.com
Sheet1

 

[martinee]

Re: Complicated IF formula (how do I combine 11 if statement

You can't nest 11 If statements in a formula. Gotta go for VBA or some other method. In VB, check out a select case statement, or you could write out the if's, no big difference there. HTH

 

[daveus]

you might also try some variation of the substitute function in excel. It might take another step or two. You're best bet will likely be writing some vba code where you can write all the ifs out.

One question, does the formula have to determine which format to use, or do you specify that so that the code only has to apply the format?

Dave...

 

[Pekkavee]

Re: Complicated IF formula (how do I combine 11 if statement

I think 7 is the max functions we can nest.

You could use VLOOKUP

Put syntaxes in column F1:F11 and Formulas in G1:G11
Then

=VLOOKUP(D2,$F$1:$G$11,2,FALSE) in Cell E2 and copy down

I hope this works.

 

[Zack Barresse]

Hello,

I know it's not the best, but it's an idea and tests ok with me. Put this into a standard module and call when you want. Change ranges as desired - ran from posted information.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> emailAddy2()

    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, e <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, g <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, h <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    a = "first.last@company.com":   b = "first_last@company.com"
    c = "first.m.last@company.com": d = "first_m_last@company.com"
    e = "flast@company.com":        f = "lastf@company.com"
    g = "fmlast@company.com":       h = "firstlast@company.com"
    j = "FLast>7@company.com":      k = "FLast>8@company.com"
    i = ActiveCell.Row
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> String: x = Range("D" & i).Value
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> x
        <SPAN style="color:#00007F">Case</SPAN> a
            Range("F" & i).Formula = "=A" & i & "&"".""&C" & i & "&(MID(D" & i & ",(FIND(""@"",D" & i & ")),50))"
        <SPAN style="color:#00007F">Case</SPAN> b
            Range("F" & i).Formula = "=A" & i & "&""_""&C" & i & "&(MID(D" & i & ",(FIND(""@"",D" & i & ")),50))"
        <SPAN style="color:#00007F">Case</SPAN> c
            Range("F" & i).Formula = "=A" & i & "&""_""&LEFT(B" & i & ",1)&""_""&C" & i & "&MID(D" & i & ",FIND(""@"",D" & i & "),50)"
        <SPAN style="color:#00007F">Case</SPAN> d
            Range("F" & i).Formula = "=A" & i & "&""_""&(IF(B" & i & "="""","""",(LEFT(B" & i & ",1))&""_""))&C" & i & "&(MID(D" & i & ",(FIND(""@"",D" & i & ")),50))"
        <SPAN style="color:#00007F">Case</SPAN> e
            Range("F" & i).Formula = "=LEFT(A" & i & ",1)&C" & i & "&MID(D" & i & ",(FIND(""@"",D" & i & ")),50)"
        <SPAN style="color:#00007F">Case</SPAN> f
            Range("F" & i).Formula = "=C" & i & "&LEFT(A" & i & ",1)&MID(D" & i & ",(FIND(""@"",D" & i & ")),50)"
        <SPAN style="color:#00007F">Case</SPAN> g
            Range("F" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(B" & i & ",1)&C" & i & "9&MID(D" & i & ",(FIND(""@"",D" & i & ")),50)"
        <SPAN style="color:#00007F">Case</SPAN> h
            Range("F" & i).Formula = "=A" & i & "&C" & i & "&(MID(D" & i & ",(FIND(""@"",D" & i & ")),50))"
        <SPAN style="color:#00007F">Case</SPAN> j
            Range("F" & i).Formula = "=(LEFT(A" & i & ",1))&(LEFT(C" & i & ",7))&(MID(D" & i & ",(FIND(""@"",D" & i & ")),50))"
        <SPAN style="color:#00007F">Case</SPAN> k
            Range("F" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(C" & i & ",8)&MID(D" & i & ",(FIND(""@"",D" & i & ")),50)"
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            Range("F" & i).Formula = """"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

edit: btw, a good way to use this, would be to use Data Validation with your Email Sytyax' as an in-cell drop down box to choose from, then run the code, probably easiest to assign to a shortcut key.

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

Dear firefytr,

I am ashamed to say that I'm a newbie. I really want to try out your code. Could you please tell me exactly where to put this code to make it work?

THANKS!

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

I think 7 is the max functions we can nest.

You could use VLOOKUP

Put syntaxes in column F1:F11 and Formulas in G1:G11
Then

=VLOOKUP(D2,$F$1:$G$11,2,FALSE) in Cell E2 and copy down

I hope this works.

Dear Pakkavee,

I tried your formula - it works for current 12 rows, but if i add new rows of data, it no longer works...

 

[alinka]

you might also try some variation of the substitute function in excel. It might take another step or two. You're best bet will likely be writing some vba code where you can write all the ifs out.

One question, does the formula have to determine which format to use, or do you specify that so that the code only has to apply the format?

Dave...

if by format you mean syntax...i want the formula to recognize which syntax to use -- basically, each row of name data has a corresponding syntax, so the formula should see the syntax, see the name, and create a corresponding email address.

 

[Pekkavee]

Re: Complicated IF formula (how do I combine 11 if statement

False thinking of me, sorry
You can not import a function with VLOOKUP, it only imports the result of the function.

You can use firefytrs code like this

Copy and paste the code to your VBA module like this:
1. Select text with your mouse
2. Ctrl + C
3. Go to your Excell
4. Alt + F11
5. Click "Insert"
6. Click "Module"
7. Paste the code to the white box with Ctrl + V

Then you can run it with F5

Or you can put a button to your worksheet by
1. Show
2. Tools
3. Form
4. Then select the button from the form tool box and tell it to run your macro.

Firefytrs macro runs on that row which you have selected.

 

[Zack Barresse]

Hello,

Thanks Pekkavee. Once you've copy/pasted into a new module, you can also run by pressing Alt + F8, select the macro (emailAddy2 in this case) and click Run.

You can also assign the macro to drawings or Auto Shapes for some creative latitude. Hope this helps.