# Using calculated field to retrieve substring of another field

Suppose I have a field that contain the following data:

```==========================
==========================
| [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 protected]    |   Jack     |   wonderland.com  |
| [email protected]   |   Peter    |   nobodyland.com  |
| [email protected]    |   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, 2012 at 11:27
• Here is a larger amount of functions for sharepoint office.microsoft.com/en-us/windows-sharepoint-services-help/…
– user15050
Feb 20, 2013 at 22:08
• is there anyway, we can do such calculation in SPD workflow?
– user17734
Jun 20, 2013 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." 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
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? 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. 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. :) Feb 13, 2012 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