Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Formula to convert name fields into email addresses in 2007

Posted on 2011-03-17
Medium Priority
295 Views
Hi Experts

How would you write a formula to convert peoples names from col A into email address with a fixed @bbb.com in col B (all names have a space between first and last name in col a)

Col A             Col B
P Keilah        P.keilah@bbb.com

thanks
0
Question by:route217
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 39

Expert Comment

ID: 35155277
write formula in colb like

=CONCATENATE(A1;"@bbb.com")
0

LVL 24

Accepted Solution

jimyX earned 500 total points
ID: 35155280
Use this in column B:
=SUBSTITUTE(A1," ",".")&"@bbb.com"
0

LVL 19

Assisted Solution

MINDSUPERB earned 500 total points
ID: 35155291
Use this formula:

=LEFT(A1,1)&"."&RIGHT(A1,LEN(A1)-2)&"@bbb.com"

Sincerely,
Ed
0

LVL 39

Assisted Solution

Pratima Pharande earned 500 total points
ID: 35155355
try this

=CONCATENATE(REPLACE(A1;SEARCH(" ";A1);1;".");"@bbb.com")
0

Author Comment

ID: 35155669
many Thanks for the feedback...
0

LVL 19

Assisted Solution

Rikin Shah earned 500 total points
ID: 35155733
Try this..

=CONCATENATE(SUBSTITUTE(A1," ","."),"@bbb.com")
0

LVL 4

Expert Comment

ID: 35165052
Hi

I would advise going a step further and use the trim function to remove extraneous spaces in the names.

=SUBSTITUTE(TRIM(A1)," ",".") & "@bbb.com"

Depending on the source of the data it is quite possible you have some extra spaces in there somewhere.

Best regards

Rowan
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
###### Suggested Courses
Course of the Month10 days, 21 hours left to enroll