+ Reply to Thread
Results 1 to 17 of 17

How would i join a space email address using a text function.

  1. #1
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Lightbulb How would i join a space email address using a text function.

    For example i have an email given like : jane [email protected], how would i remove the gap using a text function for it to be [email protected] ?

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How would i join a space email address using a text function.

    In A1 Cell

    jane [email protected]


    In B1 Cell

    =SUBSTITUTE(A1," ","")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    That gave me this =SUBSTITUTE([@[Email Address]]," ","") when i clicked enter, is it because i used a concatenate function to get the full name?

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How would i join a space email address using a text function.

    Don't click on A1, just manually type the cell address in.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How would i join a space email address using a text function.

    Another way would be to highlightthat range, press CTRL H (find/replace), Find space (press the space bar), replace with nothing
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    I just did that and it still gave me =SUBSTITUTE(e2," ",""), after i clicked enter again

  7. #7
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    so i highlight the range of all the spaced emails the click control H, the find/replace came up then in the find box i press the space button and in the replace i don't input any text then pressed enter, it told me it could not find what i'm looking for

  8. #8
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    Here you can take a look
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How would i join a space email address using a text function.

    In E, why not just use...
    =Table1[[#This Row],[Firstname]]&Table1[[#This Row],[Lastname]]&"@excelstuff.ca"

    The other suggestions wont work because you are working with a formula in that cell

  10. #10
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    Hi sorry what does #This Row mean? And first name and last name is it the direct names ?

  11. #11
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    It's still didn't work

  12. #12
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    I think the table has a setting that only when inputed gives the correct full email address

  13. #13
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    Because the question says The first type of email is in the format of “[email protected]”. Add a column called “E-Mail Address” and use a nested text function to turn the “Full Name” of an employee into the desired email.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How would i join a space email address using a text function.

    I put this in F2 and it worked fine in all rows.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does this do what you want?

  15. #15
    Registered User
    Join Date
    11-11-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    57

    Re: How would i join a space email address using a text function.

    Thanks a bunch all of u!

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How would i join a space email address using a text function.

    Quote Originally Posted by MysJee View Post
    It's still didn't work
    Your formula adds that space, so I just removed the space from your formula...
    =Table1[[#This Row],[Firstname]]&" "&Table1[[#This Row],[Lastname]]&"@excelstuff.ca"
    =Table1[[#This Row],[Firstname]]&Table1[[#This Row],[Lastname]]&"@excelstuff.ca"

  17. #17
    Registered User
    Join Date
    11-02-2014
    Location
    Nairobi
    MS-Off Ver
    2010
    Posts
    4

    Re: How would i join a space email address using a text function.

    You needed to format Column F by Highlighting --> Right Click---> Format Cells ----> An Select General. It will Work

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Removing all text except email address from one cell
    By Steve_123 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-09-2014, 07:07 AM
  2. Replies: 1
    Last Post: 03-08-2012, 01:57 PM
  3. Concatenate function to join some text strings
    By FRJ1949(Frank) in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2010, 03:30 PM
  4. How do I remove all text to the left of the @ in an email address
    By David M in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1