Formula combining CONCATENATE and IF functions

**GeezerGekk** · 03-28-2008, 10:29 PM

Hi everyone.

I am trying to kill two birds with one stone in a single formula.

I have three cells that I wish to combine to create an e-mail address cell.

A. E-mail domain
B. First name (F)
C. Last name (L)

However, a fourth cell comes in to play before cells B and C can be concatenated and that cell (D) is the e-mail convention (i.e. F.L, F_L, 1FL etc...) Where '1FL' = 'first letter of First Name followed by Last Name', or 'F1L' = 'First Name followed by first letter of Last Name', or '1F_L' = 'first letter of First Name followed by Last Name, separated by an underscore'.

I have worked out how to concatenate the fields to the point of 'First Name . Last Name @ E-mail domain' ('= B3 & "." & C3 & "@" & A3"). So, one function is sorted - adding the '@' to the e-mail domain.

What I am trying to work out is how to incorporate an IF function that will look at cell D, interrogate the e-mail convention and apply rules before concatenating cells B and C (e.g. - if e-mail convention in cell D is '1F.L', take just the first letter of First Name in cell B, then pull all of Last Name (cell C) into the resulting cell, whilst determining and adding the correct separator ('.' ; '_' ; or no separator).

There are about eighteen (18) possible combinations of e-mail convention;

1F.L
1F_L
1F1L
1FL
1LF
F
F.1L
F.L
F_1L
F_L
F1L
FL
L
L.1F
L.F
L_F
L1F
LF

Which makes the length of the formula mind boggling (for me, anyway)!

Can anyone shed some light on how this can be done, please.

Thanks,

Ed.

**protonLeah** · 03-29-2008, 02:17 AM

If you don't mind a User Defined Function, then the following will work:

Option Explicit
Public Function MakeAddr(ByVal Domain As String, ByVal FName As String, ByVal LName As String, ByVal Rule As String) As String
      Dim testvar
      For Each testvar In Worksheets(2).Range("A:A")
            If testvar = UCase(Rule) Then
                  Select Case testvar.Row
                        Case 1: MakeAddr = Left(FName, 1) & "." & LName & "@" & Domain
                        Case 2: MakeAddr = Left(FName, 1) & "_" & LName & "@" & Domain
                        Case 3: MakeAddr = Left(FName, 1) & Left(LName, 1) & "@" & Domain
                        Case 4: MakeAddr = Left(FName, 1) & LName & "@" & Domain
                        Case 5: MakeAddr = Left(LName, 1) & FName & "@" & Domain
                        Case 6: MakeAddr = FName & "@" & Domain
                        Case 7: MakeAddr = FName & "." & Left(LName, 1) & "@" & Domain
                        Case 8: MakeAddr = FName & "." & LName & "@" & Domain
                        Case 9: MakeAddr = FName & "_" & Left(LName, 1) & "@" & Domain
                        Case 10: MakeAddr = FName & "_" & LName & "@" & Domain
                        Case 11: MakeAddr = FName & Left(LName, 1) & "@" & Domain
                        Case 12: MakeAddr = FName & LName & "@" & Domain
                        Case 13: MakeAddr = LName & "@" & Domain
                        Case 14: MakeAddr = LName & "." & Left(FName, 1) & "@" & Domain
                        Case 15: MakeAddr = LName & "." & FName & "@" & Domain
                        Case 16: MakeAddr = LName & "_" & FName & "@" & Domain
                        Case 17: MakeAddr = LName & Left(FName, 1) & "@" & Domain
                        Case 18: MakeAddr = LName & FName & "@" & Domain
                  End Select
                  Exit For
            End If
      Next testvar
End Function

NOTE: As written, it requires that the list of conventions/rules be on sheet2, col A1 to A18. Change to suit.

So, in cell E1, for example, enter =MakeAddr(A1,B1,C1,D1)
and copy down your list

**GeezerGekk** · 03-31-2008, 03:04 PM

Hi Ben.

Thanks for the reply and apologies for the delay in responding. Truth is, I have absolutely no idea what to do with the answer! I've tried figuring it out (hence, the delay in replying), but I just don't know what to do with the information. Is there a menu option in Excel that would take me some where that I could paste your code?

Sorry if this is a real mundane question to you. I am only conversant with entering formula in the cell.

Cheers!

Ed.

**GeezerGekk** · 04-03-2008, 09:57 PM

Hi Ben.

Since my last post/reply, I have had a good crack at getting your code to work, but I keep getting errors.

What I have done is copy and paste your user defined function in to a new macro, changed the field names to match exactly those of the worksheet, added a second worksheet with cells A1 to A18 containing the 18 various conventions, and pasted the function =MakeAddr(A2,B2,C2, D2) in to the first cell/record/row (E2).

When I hit return on function in E2, I get an Excel error message "That name is not valid".

And when I try running the Macro, I get a Visual Basic error message stating "Compile error: Ambiguous name deteceted: MakeAddr".

Here's the macro;

What have I done wrong?! I am pleased with how far I have progressed this (as an utter novice), and I appreciate your assistance in getting me this far, but I now really disappointed in myself that I cannot get it work.

Can you please help?

Cheers!

Ed.

**protonLeah** · 04-03-2008, 11:59 PM

First, to get the function to work, it must be inserted into a new module:
In the project pane of the VB Editor, right-click the project (the one with the name of your file in parentheses), select: Insert -> module.
The paste the function into the new module. Do not paste the lines
Sub makeaddr(... or End Sub
Note: a FUNCTION is a block of code that returns a single value. For example, say you enter the sentence "this is a test" in A1, then in B1 you enter the function "=LEN(A1)". When you press Enter, B1 will show the value 14 since LEN() counts the length of the string referenced (A1, here) and returns a single number.
For this User Definded Function (UDF) it builds a string based on the parameters email_domain, first_name, etc and returns a single string: the e-mail address. SUBS do not return values, so you cannot have a sub named MakeAddr(... and enter "=MakeAddr(...) and have it return a value. So the SUB lines must be deleted. The changes you made to the field names are ok.

**GeezerGekk** · 04-06-2008, 04:04 PM

Hi Ben.

Thanks for this. I have followed your notes, but I keep getting a VB error message - "Ambiguous name detected: MakeAddr" and the result "#NAME?" and I cannot see where it applies.

Rgds,

Ed.