How to Split Pasted Names in WPS Office Excel When Text to Columns Fails
Question details
The user needs to split pasted names into columns in WPS Office Excel, but Text to Columns and Find and Replace fail because the copied data contains non-breaking spaces instead of standard spaces.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Names have been copied from a website or another application and pasted into WPS Office Excel for separation into columns.
- Observed behavior
- Text to Columns does not separate the names correctly, and Find and Replace may also fail to detect the invisible non-breaking spaces.
Select the column that contains the pasted names. Use the numeric keypad for the Alt+0160 shortcut; if your keyboard does not have a numeric keypad, enable Num Lock or use the on-screen keyboard.
Replace non-breaking spaces, then run Text to Columns
Use this method when pasted names do not split correctly because they contain non-breaking spaces.
Non-breaking spaces look identical to regular spaces but use a different character code. Because WPS Office Excel treats them as non-delimiter characters, Text to Columns and a normal Find and Replace can miss them.
Open your workbook in WPS Office Excel and select the column or range that contains the pasted names.
Press Ctrl+H to open the Find and Replace dialog.
Click in the Find what box, hold Alt, and type 0160 on the numeric keypad. The box may look empty, but the character is inserted.
Click in the Replace with box and press the Spacebar once.
Select Replace All, then close the dialog when finished.
Go to Data > Text to Columns, choose Delimited, select the delimiter you need, and finish the wizard.
Clean Non-Breaking Spaces and Split Pasted Names in WPS Office Excel
Use WPS Office Excel's Find and Replace plus Text to Columns to replace invisible non-breaking spaces and split pasted name data into separate columns without retyping the entire list.
- 1. Download WPS Office: Go to the WPS Office official download page and click Download WPS Office Free.
- 2. Open your workbook in WPS Office Excel: Launch WPS Office, open the workbook containing the pasted names, and select the name column.
- 3. Run Find and Replace and Text to Columns: Use Ctrl+H to replace non-breaking spaces with regular spaces, then go to Data > Text to Columns to split the names.
Frequently Asked Questions
Why does Text to Columns fail on names I pasted from a website?
The pasted text likely contains non-breaking spaces instead of regular spaces. WPS Office Excel does not recognize them as delimiters, and a normal Find and Replace using the Spacebar does not detect them.
How do I type a non-breaking space in the Find box?
Click in the Find what box, hold Alt, and type 0160 using the numeric keypad, then release Alt. If your keyboard has no numeric keypad, enable Num Lock or use the on-screen keyboard.
What should I do if some names still do not split correctly?
After replacing non-breaking spaces, run Data > Text to Columns again. Check entries with titles such as Dr., hyphens, initials, or multiple-word last names because these may require manual cleanup.
Can I use this method in WPS Office Excel and Microsoft Excel?
Yes. The Alt+0160 Find and Replace and Data > Text to Columns approach works in both WPS Office Excel and Microsoft Excel because the menu workflow is very similar.
