Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i have a table like below:
Emailid | First Name | Last Name |
marguerite.e.sheehan@idea.com | Marguerite |
Last Name may be blank sometimes, i need to find a formula which will say, if (Last Name is blank, then Last Name should be from email id between '.' and '@')
The problem i have here is, the email id has middle name,
my output should look like:
Emailid | First Name | Last Name |
marguerite.e.sheehan@idea.com | Marguerite | Sheehan |
i tried something with subfield, but its not giving the right answer.
Any Help?
Quickest way i can think of is :
subfield(subfield('marguerite.e.sheehan@idea.com','.',3),'@',1)
Have Fun,
GM
Quickest way i can think of is :
subfield(subfield('marguerite.e.sheehan@idea.com','.',3),'@',1)
Have Fun,
GM
I think this a bit shorter
textbetween('marguerite.e.sheehan@idea.com','.','@',2)
If you want the name capitalized, use
Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',2))
and if you have records with middle name and some without, i.e. 1 or two occurences of '.' before the '@', use something like this
=Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',
SubStringcount(left('marguerite.e.sheehan@idea.com',index('marguerite.e.sheehan@idea.com','@')),'.')))
Of course you should replace 'marguerite.e.sheehan@idea.com' with your field name.
Regards,
Stefan