How to Remove Extra Values When Exporting Microsoft Lists to Excel
Question details
The user needs to clean up name columns by removing unexpected extra characters and numbers that are appended after exporting a Microsoft List.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Exporting data from Microsoft Lists to a Microsoft Excel spreadsheet for reporting or analysis.
- Observed behavior
- Extra numeric values such as ';#3' or ';#16' appear beside people's names in the exported Excel file, which did not occur in previous exports.
Before modifying the exported data, ensure the affected column has an empty column immediately to its right so that separated data does not overwrite your existing information.
Use the Text to Columns Feature to Separate Names
This built-in Excel tool allows you to split cell contents based on specific delimiters, effectively separating the actual names from the unwanted numeric values.
The Text to Columns feature is ideal for splitting data that contains a consistent separator character, such as a semicolon.
Click the letter of the column header that contains the names with the extra values to highlight the entire column.
Navigate to the Data tab on the Excel ribbon and click on the 'Text to Columns' button located in the Data Tools group.
In the Convert Text to Columns Wizard, select 'Delimited' as your file type and click 'Next'.
Check the box for 'Semicolon' (or select 'Other' and type ';'). The data preview will show the names separated from the numbers. Click 'Next' and then 'Finish'.
Use Find and Replace to Delete the Extra Characters
If the extra characters follow a consistent pattern beginning with a specific symbol, you can quickly erase them using the Find and Replace wildcard function.
Clean Exported List Data Easily with WPS Spreadsheet
WPS Spreadsheet offers intuitive data processing tools like Text to Columns and wildcard Find and Replace, making it incredibly easy to clean and organize raw data exported from Microsoft Lists.
- 1. Open the exported file: Launch WPS Spreadsheet and open the .xlsx or .csv file exported from your Microsoft Lists.
- 2. Select the messy column: Click the column header containing the names with the appended numeric values to highlight it.
- 3. Launch Text to Columns: Go to the Data tab on the top ribbon and select the 'Text to Columns' option.
- 4. Split and clean the data: Follow the wizard prompts, select 'Semicolon' as your delimiter, and click Finish to isolate the clean names.
Frequently Asked Questions
Why do extra characters like ';#3' appear in my exported Microsoft List?
These characters are system lookup IDs generated by SharePoint or Microsoft Lists to manage user metadata internally. When the data is exported to a raw spreadsheet format, these internal IDs sometimes become visible as appended text.
Can I prevent these extra values from exporting in the first place?
Sometimes, changing the view in Microsoft Lists before exporting or ensuring the column is strictly set to display 'Name (with presence)' can help. However, raw data exports often pull the lookup IDs regardless, making post-export cleanup necessary.
Does the Text to Columns method affect the original file?
Modifying data in Excel or WPS Spreadsheet only affects the downloaded file. Your original Microsoft List remains completely unchanged and safe on the server.
What if my data has multiple semicolons in a single cell?
If a cell contains multiple semicolons, the Text to Columns feature will split the data into multiple new columns. In such cases, using the Find and Replace method with the specific ';#*' wildcard is often safer and more accurate.
