• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Excel Help with formula building smtp

Dear experts,

I need to change the formula below to build the correct smtp address. At the moment the default is a comma and a blank for example: lastName, firstName
Cell B3 is: jones, mike
Cell B18 is: gul.com
Result is :  mike.jones@gul.com

So far so good but it should be also working if B3 is:  jonesmike or jones mike the result should be jonesmike@gul.com
only in case of jones, mike it should be mike.jones@gul.com

Could anybody help me with this one?

Thanks in advance.

=SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)

=SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)

Open in new window

0
Mandy_
Asked:
Mandy_
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&B18)


Regards
0
 
Mandy_Author Commented:
Functioning properly. Many many thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now