Export custom list to excel

Question

I'm using SharePoint 2007. I have created custom-list.

The list contains one column from "Person and group" type, show field: E-mail.

I fill in data in the list column and export this list to excel list.

In the Excel list, I see the Email with employee number.

It looks like: firstName_LastName_CW@bmc.com;#397

How can I export a custom list into Excel without the number?

Answer 1

I had a similar situation when I was working on workflows. In my workflow I wanted to attach the name of the employee. I inserted the employee field (Column Type: Person/Group) and got more data than I needed.

The reason is that the Column Type Person/Group was a String and in that string it contains information on the name, the email, the id (for you thats #397) and the login name. In sharepoint, your list is able to determine which details you wanted (ie. email only) but when you export to Excel it will export the whole thing.

For the workflow I had to the ability to set a mask 'Return Field As.' For you its going to be little different. You would think that the simple solution would be to create a calculated Field that looks at the information Person Group column and retrieves the email only. Unfortunately for you, Sharepoint does not allow for a calculated field based on a Person/Group. So, you will actually be joining my methods by creating a workflow to do the parsing for you.

Read this: http://social.technet.microsoft.com/Forums/sharepoint/en-US/a463eefe-4a07-40b7-a489-b896b93219ea/workaround-to-using-person-or-group-field-in-a-calculated-column-formula

1. Create your soon to be populated column as Type Text
2. Create a simple workflow to auto-populate; read the article I gave you 2b. Note that when you work with the workflow be mindful of 'Return Field As'

I hope this helps.

Answer 2

Use calculated fields in excel sheet. Or in sharepoint list