Suppose I have a field that contain the following data:

|  Email Address         |
| Jack@wonderland.com    |
| Peter@nobodyland.com   |
| Jill@wonderland.com    |

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       |
| Jack@wonderland.com    |   Jack     |   wonderland.com  |
| Peter@nobodyland.com   |   Peter    |   nobodyland.com  |
| Jill@wonderland.com    |   Jill     |   wonderland.com  |
  • 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 Oct 22 '12 at 11:27
  • Here is a larger amount of functions for sharepoint office.microsoft.com/en-us/windows-sharepoint-services-help/… – user15050 Feb 20 '13 at 22:08
  • is there anyway, we can do such calculation in SPD workflow? – user17734 Jun 20 '13 at 5:50
  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.

  • 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 Feb 13 '12 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 Feb 13 '12 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 Feb 13 '12 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 Feb 13 '12 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 Feb 13 '12 at 8:13

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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