How to Split Excel Text Using a Hyphen Delimiter
Question details
The user needs to separate text strings containing movie titles and locations stored in a single column into two distinct columns using a hyphen as the delimiter.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Separating combined data strings, such as 'Movie title - page 33', into discrete columns for better data organization and analysis.
- Observed behavior
- The data is currently merged in one column with a hyphen separating the two distinct pieces of information, requiring extraction into separate columns.
Ensure there are empty columns directly to the right of your target data so that the split text does not overwrite your existing information.
Use the Text to Columns Feature
The most straightforward method to split text by a specific character like a hyphen.
The Text to Columns wizard allows you to define custom delimiters, making it easy to split data exactly where the hyphen is located.
Highlight the column or specific cells containing the text you want to split.
Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'.
In the wizard, select the 'Delimited' option and click 'Next'.
Check the box for 'Other', type a hyphen (-) in the input box next to it, and click 'Finish'.
Extract Text Using Formulas
Ideal for dynamic data where you want the split columns to update automatically if the original text changes.
Easily Split Text and Manage Data with WPS Office
WPS Spreadsheet offers powerful, easy-to-use data tools like Text to Columns and dynamic text functions, making it a perfect tool for organizing merged data.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your merged text.
- 2. Select your cells: Highlight the column or cells you wish to split.
- 3. Use Text to Columns: Go to the 'Data' tab, select 'Text to Columns', choose 'Delimited', and enter a hyphen in the 'Other' field before clicking 'Finish'.
Frequently Asked Questions
Why isn't Text to Columns splitting my data at the hyphen?
Your data might contain em-dashes or en-dashes (often copied from Word or web pages) instead of a standard hyphen. Use Find and Replace to replace the special dashes with a standard minus sign/hyphen before using Text to Columns.
How can I split text if some cells have multiple hyphens?
If there are multiple hyphens, the Text to Columns feature will split the data into a new column for every hyphen it finds. If you only want to split at the first hyphen, using specialized formulas with LEFT, RIGHT, and SEARCH is recommended.
Will splitting my column delete the original data?
By default, Text to Columns replaces the original column with the first part of the split text. To keep the original data, copy it to a new column before splitting, or use the formula method which extracts the text into new cells while leaving the original cell intact.
