Change character while copy

M

mnr

Hi!
Given are the following columns e.g.: Surname and Name.
In a third column I would like to paste the contents of these cell
together to create an email addresses.

Example:
Column1: john
Column2: vön humboldt
Column3(=result): [email protected]

The problems that arise and aren't solved yet:
PROBLEM1:
the spaces should be removed
PROBLEM2:
Special characters should be replaced. Writing a formula containing
sub-formula for every character that should be replaced is quit
inefficient, resulting in a large formula. So is there an alternative
A standard formula, trick,...?
to give you an idea:
ü >(should be replaced by)> u
éêëè > e
âäàå > a
ç > c
etc...

lookings forward for solutions. thanks in advance!

Rene
 
P

PeterAtherton

mnr said:
Hi!
Given are the following columns e.g.: Surname and Name.
In a third column I would like to paste the contents of these cells
together to create an email addresses.

Example:
Column1: john
Column2: vön humboldt
Column3(=result): [email protected]

The problems that arise and aren't solved yet:
PROBLEM1:
the spaces should be removed
PROBLEM2:
Special characters should be replaced. Writing a formula containing a
sub-formula for every character that should be replaced is quite
inefficient, resulting in a large formula. So is there an alternative?
A standard formula, trick,...?
to give you an idea:
ü >(should be replaced by)> u
éêëè > e
âäàå > a
ç > c
etc...

lookings forward for solutions. thanks in advance!

Renee
Click to expand...
Renee

Assuming the name is on row 5 columns A and B then
=LOWER(A5&SUBSTITUTE(B5," ",""))&"@"

Use Edit, Replace to change é to e and repeat for each character

regards
Peter
 
Top