[ms office - 2010] Excel Formulas display as text

[Tesla]

Hi all

 

IU have been given a list of first and last names in an excel sheet, and i have to add their email address to each one.

 

Their email is in the form of "Firstname.Lastname@*******.*******.sch.uk"

 

Is there an easy way to make the email cell include the text from the tgwo name cells with the . inbetween?

 

(no idea if what i've wrote make sense)

[Joanne]

=CONCATENATE(a1),.,(A2)@*****.SCH.UK

 

something like that... lemme do a test.

 

edit: =CONCATENATE(A2,".",B2,"@****.sch.uk")

Edited July 21, 2014 by Joanne

[Seb1780]

=CONCATENATE(a1),.,(A2)@*****.SCH.UK

 

something like that... lemme do a test.

 

Yes, CONCATENATE is the way to do it; you will need to put the full stop in quotes though:-

 

=CONCATENATE(CellRefFirstname,".",CellRefSecond Name,"@domain")

[LosOjos]

Just as an alternative to CONCATENATE, you can use ampersand to join text too e.g.

 

=A1&"."&A2&"@school.sch.uk"

[Tesla]

Cheers guys, both worked like a charm saved me doing ~200 names manually.

 

Knew there would be a way

[Seb1780]

Just as an alternative to CONCATENATE, you can use ampersand to join text too e.g.

 

=A1&"."&A2&"@school.sch.uk"

 

You learn something new every day!

[Joanne]

little tricks like this save your life when it comes to creating a full school of new users!

[plexer]

Indeed I've always used & to concatenate strings when using excel to generate usernames.

 

Ben

[plexer]

You can also use char to insert characters.

 

=A1&char(46)&A2&"@school.sch.uk"

 

Ben

[Arthur]

Here's another formula you might find useful.

 

=LOWER(SUBSTITUTE(SUBSTITUTE(CONCATENATE($A2,".",$B2,"@school.sch.uk"), " ",""),"'",""))

 

 

As you can see from the screenshot above, it removes apostrophes and spaces from names and makes the entire e-mail address lowercase.

[Tesla]

OOO, very good point, some of those on the lists have names like above....

 

to be more of a pain, how would you get rid of hyphenated names in the sheet? (we have a hyphen in the @school bit, that wont be affected will it?)

 

Thanks

 

 

EDIT: It is affected, it takes the hyphen out of the end part of email address too

Edited July 21, 2014 by Tesla

[sonofsanta]

Use

SUBSTITUTE(A1,"-","")

where A1 is the cell with the surname in to replace any - with nothing. Use that chunk in place of the usual cell reference.

 

I actually use

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),"'","")

to strip out any spaces, dashes and apostrophes. Windows allows them, but they can cause grief with other systems (e.g. 365, Moodle, whatever).

 

EDIT: which is more or less what @Arthur said above, you coulda worked it out from that and it won't affect the school domain suffix if you're not running that through the SUBSTITUTE().

 

EDIT EDIT: Right - let's say you have a first name in A1 and a surname in A2, your formula would be

=A1&"."&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"'","")&"@school-name.county.sch.uk"

 

Wrap all that in a LOWER() if you want it in lower case.

Edited July 21, 2014 by sonofsanta

[Tesla]

Cheers, as i mentioned (edited so may of missed it), annoyingly we have a hyphen in our domain part of the email.

[sonofsanta]

Cheers, as i mentioned (edited so may of missed it), annoyingly we have a hyphen in our domain part of the email.

 

If you copied @Arthur's formula, it was because the substitute there wrapped around the whole concatenated address - use the formula from my second edit above and it only applies the substitute to the surname. Or, if you have people with dashes in their first name as well,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"."&A2," ",""),"-",""),"'","")&"@school-name.county.sch.uk

strips out silly characters from both names before appending the domain name as written.

[Tesla]

Got there in the end, i'd just got rid of the hyphen(s) before your post @sonofsanta, so this thread now looks a tad messy.... but i have got what i need from it. Thanks a lot people. =]