How to Split Combined PDF Data into Separate Excel Columns
Question details
The user needs to separate combined values into distinct Excel columns after converting a PDF to an XLSX file.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Editing an Excel spreadsheet that was recently converted from a PDF, where two distinct data points were merged into a single cell.
- Observed behavior
- Values that were expected to be distributed across two separate columns remain merged in one column, such as '12m 7s' appearing together in a single cell.
Ensure your converted Excel file is open and you have identified the specific columns where the combined data needs to be separated. It is highly recommended to insert a few empty columns to the right of your target data to prevent the split values from overwriting adjacent cells.
Use the Text to Columns Feature
The most straightforward method to split combined text in Excel after a PDF conversion is by using the built-in Text to Columns tool with a specific delimiter like a space.
Because PDF files rely on visual layout rather than strict table structures, the conversion process sometimes groups nearby text into a single cell. The Text to Columns feature easily resolves this by separating the text based on a character you specify.
Highlight the entire column or the specific range of cells containing the combined data (e.g., '12m 7s').
Navigate to the 'Data' tab located on the top ribbon menu and click on the 'Text to Columns' button.
In the wizard that appears, select 'Delimited' if your data is separated by spaces, commas, or other characters, then click 'Next'.
Check the box next to 'Space' (or the relevant delimiter for your data). You will see a preview of how your data will be separated. Click 'Finish' to apply the split.
Use the TEXTSPLIT Formula
If you prefer a dynamic solution that preserves your original data and automatically updates, you can use the TEXTSPLIT function to separate the combined values.
Easily Split Data with WPS Spreadsheet
WPS Office provides seamless PDF to Excel conversion and powerful data management tools. You can effortlessly clean up and split combined data using the intuitive Text to Columns feature in WPS Spreadsheet.
- 1. Open the converted file: Launch WPS Spreadsheet and open the Excel file you recently converted from PDF.
- 2. Select the target cells: Highlight the cells or the entire column containing the combined values you wish to separate.
- 3. Access the Data tab: Click on the 'Data' tab on the top ribbon and select the 'Text to Columns' option.
- 4. Split the data: Follow the on-screen wizard, select the appropriate delimiter (such as a space), and finish the process to format your data accurately.
Frequently Asked Questions
Why does PDF conversion merge two columns into one?
PDF files primarily store visual layout data rather than structural spreadsheet tables. During conversion, if the gap between two text elements is small, the conversion engine may interpret them as a single continuous string of text rather than two separate columns.
Can I split data using characters other than a space?
Yes, the Text to Columns feature allows you to choose various standard delimiters such as commas, semicolons, and tabs. You can also define a custom delimiter by typing any specific character into the 'Other' box within the wizard.
How do I split data based on a specific position instead of a delimiter?
In the first step of the Text to Columns wizard, select 'Fixed width' instead of 'Delimited'. This option allows you to manually draw vertical lines exactly where you want the text to be split, regardless of the characters present.
