Help creating a formula

[thewiseguy]

Hi everyone. Here is what I am looking to do: In cell A1 and B1, I have the following data

A1
John Smith

B1
ABC Company

I am looking for cell C1 to auto fill this structure:

jsmith@abc.com


Is there a formula I can use for this?

Thanks in advance!

 

[Joe4]

Sure it can be done, if you can define the method/logic behind it.
It looks like the left-half of the email will always be first initial & last name. But what is the logic for the right half?
You gave one basic example, but what about for company names with different name formats?

 

[thewiseguy]

sorry, i should have been more clear. the second half of the formula should return the name of the company (abc) + .com

is that more clear?

thanks.

 

[Joe4]

Still not so clear. It is important to consider all possibilities, such as:

Are you deciding to drop certain words (like "Company)?
What if the name of the company has multiple words (like "Merrill Lynch")?
What do you want to replace spaces with?

 

[thewiseguy]

ok sorry:

the first half of the formula will always be the first letter of the persons first name and the full last name so:

John Smith = jsmith

the second half of the formula will be the full "word" - whether its a company with spaces or multiple words, and then a ".com" after it

widgetsusa = widgetsusa.com


[if I can see what the formula is for this example...I can tweak it for multiple uses]

 

[Joe4]

So, would "Merrill Lynch" be "@merrill.com" or "@merrilllynch.com"?

 

[zergcow]

Based on what you have said I would use this:

=LOWER(LEFT(A1,1)&MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))&"@"&LEFT(B1,LEN(B1)-8)&".com")

To split it out. The lower is to make the whole thing lower case. the & symbols combine text (using excel 2013 here) and then the rest is just deleting spaces and removing things like the word company. Now, I can also think of a more elegant solution that uses the substitute function. As an example, use substitute to replace words like "company" with a " " ... then use trim and clean to remove those before doing something to the string. If you want an example like that let me know.

 

[thewiseguy]

So, would "Merrill Lynch" be "@merrill.com" or "@merrilllynch.com"?

merrilllynch.com

 

[thewiseguy]

Based on what you have said I would use this:

=LOWER(LEFT(A1,1)&MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))&"@"&LEFT(B1,LEN(B1)-8)&".com")

To split it out. The lower is to make the whole thing lower case. the & symbols combine text (using excel 2013 here) and then the rest is just deleting spaces and removing things like the word company. Now, I can also think of a more elegant solution that uses the substitute function. As an example, use substitute to replace words like "company" with a " " ... then use trim and clean to remove those before doing something to the string. If you want an example like that let me know.

so this worked really well. however 2 things:

1- it keeps spaces intact. Is there a way to automatically remove spaces?
2- it looks like it has a character number cutoff, as when I tried it in an example a few of the last values (before the .com) were cutoff.

 

[Joe4]

Here you go:

=LOWER(SUBSTITUTE(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,LEN(A1)) & "@" & B1 & ".com"," ",""))

Note that things may get screwy if you have more than one space in the name (like "John Paul Jones"). It may not look the way you expect it to (jpauljones@...)