19

Suppose I have a field that contain the following data:

==========================
|  Email Address         |
==========================
| [email protected]    |
| [email protected]   |
| [email protected]    |
==========================

Is there a calculated formula in SharePoint that will give me the following result? (It will help me to retrieve the sub-string from the Email Address column and give me the answer as shown in the UserName and Domain column):

===========================================================
|  Email Address         |  UserName  |      Domain       |
===========================================================
| [email protected]    |   Jack     |   wonderland.com  |
| [email protected]   |   Peter    |   nobodyland.com  |
| [email protected]    |   Jill     |   wonderland.com  |
===========================================================
Improve this question
3
  • Can I perfom the same operation i.e. =LEFT([Email Address],INT(FIND("@",[Email Address])-1)) on a lookup field of a sharepoint List?
    – user11508
    Commented Oct 22, 2012 at 11:27
  • Here is a larger amount of functions for sharepoint office.microsoft.com/en-us/windows-sharepoint-services-help/…
    – user15050
    Commented Feb 20, 2013 at 22:08
  • is there anyway, we can do such calculation in SPD workflow?
    – user17734
    Commented Jun 20, 2013 at 5:50

2 Answers 2

Reset to default
25

  1. Create Calculated column called "UserName" and type in this formula

    =LEFT([Email Address],INT(FIND("@",[Email Address])-1))

  2. Create Calculated column called "Domain" and type in this formula

    =RIGHT([Email Address],LEN([Email Address])-INT(FIND("@",[Email Address])))

It should work.

Improve this answer
6
  • What about this note from documentation (msdn.microsoft.com/en-us/library/bb862071.aspx): "Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax. However, Microsoft supports only those functions mentioned on this page for use in SharePoint Foundation calculated fields. For example, the Excel functions MID and FIND are not supported."
    – Alex Boev
    Commented Feb 13, 2012 at 6:47
  • Thanks Alex and Deepu. Hi Deepu, your formula work perfectly. I never expect that one can use INT together with FIND.
    – Jack
    Commented Feb 13, 2012 at 6:57
  • I've tried myself this: Find("1", [MyColumnName]) and have got the exception "The formula contain syntax error or isn't supported" exception. It appears that FIND is really not supported. Deepu Nair, what do you think about it? Did you try it yourself?
    – Alex Boev
    Commented Feb 13, 2012 at 7:03
  • Once again - please somebody explain me how can it work? :) Please provide tested example with FIND - I cannot make it works myself and documentation states that it's not supported.
    – Alex Boev
    Commented Feb 13, 2012 at 7:19
  • Yes, I had tried it myself and it is working correctly just as how its working for Jack. If you search the microsoft documentation, for "FIND", you can find examples of how its implemented. I believe what the article states as not supported is just for SharePoint Foundation and not for all versions. Hope its clear. :)
    – Deepu Nair
    Commented Feb 13, 2012 at 8:13
1

It appears that there's no such functions in the list of supported functions: http://msdn.microsoft.com/en-us/library/bb862071.aspx. I can offer a workaround - you can create custom field with modified XSLT to achieve desired effect. Here's the example: http://www.intheknow.it/Default.aspx?Page=customxsltfieldrendering&NS=&AspxAutoDetectCookieSupport=1

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.