View original (French)

Generate email address Excel

Apneegirl -  
m@rina Posted messages 27617 Registration date   Status Moderator 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 answers

  1.
    Ricky38 Posted messages 5776 Registration date   Status Contributor 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
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor 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
    2. 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