Change character while copy

[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 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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[olasa]

I don't like this solution, but it might solve your problem:
See enclosed: http://www.excelforum.com/attachment...tid=3482&stc=1

Ola

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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

[PeterAtherton]

"mnr" wrote:

>
> 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
>
>
> --
> mnr
> ------------------------------------------------------------------------
> mnr's Profile: http://www.excelforum.com/member.php...o&userid=24227
> View this thread: http://www.excelforum.com/showthread...hreadid=378423
>
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