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

 

[Zack Barresse]

Ok alinka, try this out:


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

    <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> Range
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    i = ActiveCell.Row
    <SPAN style="color:#00007F">Set</SPAN> c = Range("G" & i)
    a = Range("E" & i).Value
    b = a & "@company.com"
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> b
        <SPAN style="color:#00007F">Case</SPAN> "first.last@company.com"
            Range("G" & i).Formula = "=A" & i & "&"".""&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "first_last@company.com"
            Range("G" & i).Formula = "=A" & i & "&""_""&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "first.m.last@company.com"
            Range("G" & i).Formula = "=A" & i & "&"".""&(IF(B" & i & "="""","""",(LEFT(B" & i & ",1))&"".""))&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "first_m_last@company.com"
            Range("G" & i).Formula = "=A" & i & "&""_""&(IF(B" & i & "="""","""",(LEFT(B" & i & ",1))&""_""))&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "flast@company.com"
            Range("G" & i).Formula = "=LEFT(A" & i & ",1)&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "lastf@company.com"
            Range("G" & i).Formula = "=C" & i & "&LEFT(A" & i & ",1)&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "fmlast@company.com"
            Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(B" & i & ",1)&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "firstlast@company.com"
            Range("G" & i).Formula = "=A" & i & "&C" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "lastfirst@company.com"
            Range("G" & i).Formula = "=C" & i & "&A" & i & "&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "FLast>7@company.com"
            Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(C" & i & ",7)&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> "FLast>8@company.com"
            Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(C" & i & ",8)&""@""&F" & i
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            Range("G" & i).Formula = "No Set Syntax"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    c.Value = c.Value
    ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= _
        "mailto:" & c, TextToDisplay:=c.Text

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


The only thing with this one is, you need to have the cell selected where you want the address to go, in column G. And works ok for me, hope it helps.

Mike, yes I tried mentioning that earlier, that I pieced part of your code in, with the hyperlinking and hardcoding the Case's. Great work! (as usual!)

Let me know how this works alinka!

 

[Zack Barresse]

Btw, fwiw as a useful tip (if you don't do this already) you could also assign it a shorcut key. If you press Alt + F8, select the macro in question (emailAddyx) -> Options, in the 'Ctrl +' field type in the letter of the keyboard you wish for a shortcut key. I'd suggest Alt + k or Alt + m, something that isn't really used already, like Alt + S is Save type of thing.