Formula to extract Lotus Notes username

[Glaswegian]

Hi

I'm looking at e-mailing from Excel using Lotus Notes (I know, but I have to work with what I've got) and have one problem.

The code for Notes includes a line to parse the username to find the Notes Database name. However, the username does not appear correctly. The original line of code is

username = session.username
MailDbName = Left$(username, 1) & Right$(username, (Len(username) - InStr(1, username, " "))) & ".nsf"

Stepping through the code shows that the username starts as

CN=John Smith/OU=AB/O=XYZ_EUROPE

The actual username within Notes should be

John Smith/AB/XYZ_EUROPE

and this is what I need to identify the user database.

I'm not much good at formulae so I'd be grateful if anyone could suggest the changes required to end up with the correct username.

Thanks.

[Bob Phillips]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"CN=",""),"OU=",""),"O=",""

)

[Glaswegian]

Hi xld

Thanks.

When I put your formula in the code I get a "Function not defined" message.

MailDbName = Substitute(Substitute(Substitute(username, "CN=", ""), "OU=", ""), "O=", "") & ".nsf"

No doubt something I've done or not done...

[Bob Phillips]

When you said formula, I presumed that you meant Excel.

In VBA, it would look like

MailDbName = Replace(Replace(Replace(username, "CN=", ""), "OU=", ""), "O=", "")

[Glaswegian]

Hi

Apologies for not being clearer - that works great. Many thanks (as always).

[Bob Phillips]

Hi

Apologies for not being clearer - that works great. Many thanks (as always).

I wasn't criticising Iain, just getting my excuses in for why I gave you a formula not VBA

[Glaswegian]

Lol - no worries (I didn't think you were being critical anyway...)