+ Post New Thread
Results 1 to 15 of 15
Office Software Thread, Excel Formulas display as text in Technical; Hi all IU have been given a list of first and last names in an excel sheet, and i have ...
  1. #1
    Tesla's Avatar
    Join Date
    Nov 2013
    Location
    Milton Keynes
    Posts
    861
    Thank Post
    118
    Thanked 146 Times in 115 Posts
    Blog Entries
    1
    Rep Power
    61

    Excel Formulas display as text

    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)

  2. #2
    Joanne's Avatar
    Join Date
    Nov 2007
    Location
    Lancashire
    Posts
    1,610
    Thank Post
    136
    Thanked 144 Times in 124 Posts
    Blog Entries
    17
    Rep Power
    79
    =CONCATENATE(a1),.,(A2)@*****.SCH.UK

    something like that... lemme do a test.

    edit: =CONCATENATE(A2,".",B2,"@****.sch.uk")
    Last edited by Joanne; 21st July 2014 at 11:36 AM.

  3. Thanks to Joanne from:

    Tesla (21st July 2014)

  4. #3
    Seb1780's Avatar
    Join Date
    May 2013
    Location
    Suffolk
    Posts
    232
    Thank Post
    99
    Thanked 95 Times in 72 Posts
    Rep Power
    95
    Quote Originally Posted by Joanne View Post
    =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")

  5. Thanks to Seb1780 from:

    Tesla (21st July 2014)

  6. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,492
    Thank Post
    1,451
    Thanked 1,197 Times in 812 Posts
    Rep Power
    711
    Just as an alternative to CONCATENATE, you can use ampersand to join text too e.g.

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

  7. 3 Thanks to LosOjos:

    Seb1780 (21st July 2014), Tesla (21st July 2014), themightymrp (21st July 2014)

  8. #5
    Tesla's Avatar
    Join Date
    Nov 2013
    Location
    Milton Keynes
    Posts
    861
    Thank Post
    118
    Thanked 146 Times in 115 Posts
    Blog Entries
    1
    Rep Power
    61
    Cheers guys, both worked like a charm saved me doing ~200 names manually.

    Knew there would be a way

  9. #6
    Seb1780's Avatar
    Join Date
    May 2013
    Location
    Suffolk
    Posts
    232
    Thank Post
    99
    Thanked 95 Times in 72 Posts
    Rep Power
    95
    Quote Originally Posted by LosOjos View Post
    Just as an alternative to CONCATENATE, you can use ampersand to join text too e.g.

    Code:
    =A1&"."&A2&"@school.sch.uk"
    You learn something new every day!

  10. #7
    Joanne's Avatar
    Join Date
    Nov 2007
    Location
    Lancashire
    Posts
    1,610
    Thank Post
    136
    Thanked 144 Times in 124 Posts
    Blog Entries
    17
    Rep Power
    79
    little tricks like this save your life when it comes to creating a full school of new users!

  11. #8

    plexer's Avatar
    Join Date
    Dec 2005
    Location
    Norfolk
    Posts
    13,679
    Thank Post
    661
    Thanked 1,626 Times in 1,455 Posts
    Rep Power
    422
    Indeed I've always used & to concatenate strings when using excel to generate usernames.

    Ben

  12. #9

    plexer's Avatar
    Join Date
    Dec 2005
    Location
    Norfolk
    Posts
    13,679
    Thank Post
    661
    Thanked 1,626 Times in 1,455 Posts
    Rep Power
    422
    You can also use char to insert characters.

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

  13. #10


    Join Date
    Feb 2007
    Location
    51.403651, -0.515458
    Posts
    9,035
    Thank Post
    231
    Thanked 2,700 Times in 1,994 Posts
    Rep Power
    792
    Here's another formula you might find useful.

    Code:
    =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.

  14. Thanks to Arthur from:

    Tesla (21st July 2014)

  15. #11
    Tesla's Avatar
    Join Date
    Nov 2013
    Location
    Milton Keynes
    Posts
    861
    Thank Post
    118
    Thanked 146 Times in 115 Posts
    Blog Entries
    1
    Rep Power
    61
    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
    Last edited by Tesla; 21st July 2014 at 03:17 PM.

  16. #12

    sonofsanta's Avatar
    Join Date
    Dec 2009
    Location
    Lincolnshire, UK
    Posts
    4,993
    Thank Post
    866
    Thanked 1,452 Times in 999 Posts
    Blog Entries
    47
    Rep Power
    642
    Use
    Code:
    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
    Code:
    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
    Code:
    =A1&"."&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"'","")&"@school-name.county.sch.uk"
    Wrap all that in a LOWER() if you want it in lower case.
    Last edited by sonofsanta; 21st July 2014 at 03:21 PM.

  17. Thanks to sonofsanta from:

    Tesla (21st July 2014)

  18. #13
    Tesla's Avatar
    Join Date
    Nov 2013
    Location
    Milton Keynes
    Posts
    861
    Thank Post
    118
    Thanked 146 Times in 115 Posts
    Blog Entries
    1
    Rep Power
    61
    Cheers, as i mentioned (edited so may of missed it), annoyingly we have a hyphen in our domain part of the email.

  19. #14

    sonofsanta's Avatar
    Join Date
    Dec 2009
    Location
    Lincolnshire, UK
    Posts
    4,993
    Thank Post
    866
    Thanked 1,452 Times in 999 Posts
    Blog Entries
    47
    Rep Power
    642
    Quote Originally Posted by Tesla View Post
    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,
    Code:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"."&A2," ",""),"-",""),"'","")&"@school-name.county.sch.uk
    strips out silly characters from both names before appending the domain name as written.

  20. 2 Thanks to sonofsanta:

    Arthur (21st July 2014), Tesla (21st July 2014)

  21. #15
    Tesla's Avatar
    Join Date
    Nov 2013
    Location
    Milton Keynes
    Posts
    861
    Thank Post
    118
    Thanked 146 Times in 115 Posts
    Blog Entries
    1
    Rep Power
    61
    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. =]

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Report Design - Export To Excel converts numbers as Text
    By bwfc_nottingham in forum MIS Systems
    Replies: 6
    Last Post: 30th April 2013, 04:46 AM
  2. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  3. Exchange - Meeting Requests displaying as text
    By sj_somerset123 in forum Enterprise Software
    Replies: 3
    Last Post: 29th June 2012, 12:04 PM
  4. MS exchange webmail displayed as text.
    By The_Traveller in forum Windows
    Replies: 19
    Last Post: 12th February 2008, 11:10 AM
  5. URLblacklist.com as text file
    By adamyoung in forum How do you do....it?
    Replies: 11
    Last Post: 9th February 2006, 02:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •