View original (French)

Generate email address Excel

Apneegirl -  
m@rina Posted messages 27232 Registration date   Status Modérateur Last intervention   -
Hello!!

I want to create an "email address" cell from a dropdown cell containing Names and First Names.

My result should be in the form:

maurice.dupont@worldcompagnie.com

All I need is to insert the dot. How do I do that?

Here's what I have so far:

=CONCATENATE(LOWER(H8);"@worldcompagnie.com")

Configuration: Windows XP / Internet Explorer 7.0

2 réponses

Réponse 1 / 2
Ricky38 Posted messages 5776 Registration date   Status Contributeur Last intervention   1 463
 
Hello,

according to your formula in H8 you have mauricedupont

The simplest would be to have the first name, last name, and the dot in separate cells

example:
A1 = Maurice
B1 = Dupont
AA1 = .

your formula would therefore be
=CONCATENATE(LOWER(A1);$AA$1;LOWER(B1);"@worldcompagnie.com")

--
Every problem has a solution...you just have to be persistent.
10
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Good evening
Ricky38 is right, because no formula will handle compound names such as Jean Marie De la Villanelle where it is not possible to place the period correctly.
If you cannot handle the issue in separate columns, here is a formula that will convert to lowercase and replace the first space between two texts of the name or the first name with a period.
You must therefore first address any extraneous spaces due to the composition of the name and first name in the name and first name columns.
This can only be done based on the composition of the email address (hyphen or other?)
so the formula for a name in A1 and the email complement in B1
in C1

=LOWER(MID(A1,1,FIND(" ",A1)-1)&"."&MID(A1,FIND(" ",A1)+1,50))&B1

others:

_a solution to apply Ricky's solution without too much work, but still after cleaning up extraneous spaces:
select the non-first name column and Data / Text to Columns / Next /
check space and finish
your names and first names will be split into two columns.

_Finally, one last option with just the name and first name column, but still after cleaning up extraneous spaces:
select the column
Edit / Find and Replace /
at the top type a space
at the bottom type a period
and "replace all"
your names and first names will then be separated by a period. You will just need to link the whole to your email address.

for additional information:
concatenating A1 and B1 is easily written as:
=A1&B1

Best regards
0
apneegirl
 
As it involves choosing a person from a dropdown list, I can't separate the first name and the last name. Otherwise, I would have done it like Ricky 38!

I will choose to write the first and last names with a dot.
0