How to Use Excel Text to Columns with a Fixed Width from the Right
Question details
The user wants to separate text data into different columns based on a specific number of characters counted from the right side of the cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting and cleaning text data where the target string segment is located at the very end of the cell with a consistent length.
- Observed behavior
- Excel's built-in Text to Columns feature only supports parsing fixed widths from left to right, making it impossible to directly set a fixed breakline from the right side of the string.
Since the built-in Text to Columns wizard only counts characters from the left, ensure you have at least two blank columns adjacent to your data to safely extract and store the separated text using formulas.
Use RIGHT, LEFT, and LEN Formulas (Recommended)
Since Text to Columns doesn't support parsing backwards, using a combination of text extraction formulas is the most accurate and reliable workaround.
This method uses the RIGHT function to pull the exact number of characters from the end of your text, and a combination of LEFT and LEN to extract whatever remains at the beginning.
Select the first empty cell next to your data (e.g., B1). Type =RIGHT(A1, 5), replacing '5' with the fixed number of characters you want to split from the right, and press Enter.
Select the next empty cell (e.g., C1). Type =LEFT(A1, LEN(A1)-5), again replacing '5' with your fixed width, and press Enter.
Highlight cells B1 and C1, click and hold the fill handle (the small square at the bottom-right corner of the selection), and drag it down to apply the formulas to the rest of your data.
Select the new columns, press Ctrl+C to copy, right-click the same selection, and choose 'Paste as Values' to remove the underlying formulas and keep only the text.
Use Excel's Flash Fill Feature
If your data format is visually consistent, Flash Fill can automatically recognize the pattern and extract characters from the right without needing complex formulas.
Easily Split and Manage Data with WPS Office
WPS Spreadsheet provides powerful text functions and intelligent Flash Fill capabilities to help you effortlessly split, clean, and organize complex data sets. It acts as a highly compatible solution for your daily data processing tasks.
- 1. Open your data: Launch WPS Spreadsheet and open the document containing the text you need to split.
- 2. Insert blank columns: Right-click the column header next to your data and select 'Insert Column' to create space for the split text.
- 3. Apply text formulas: Use the =RIGHT(cell, number) formula to extract the exact fixed width from the end of your string.
- 4. Retrieve the remainder: In the adjacent column, type =LEFT(cell, LEN(cell)-number) to seamlessly isolate the rest of the text.
- 5. Drag to fill: Double-click the fill handle on the selected formula cells to instantly apply the split across thousands of rows.
Frequently Asked Questions
Why doesn't Excel's Text to Columns have a 'Right to Left' fixed width option?
The traditional Text to Columns wizard was designed for left-to-right parsing, standard in Western languages and typical database exports. Microsoft has not updated this legacy tool to include a reverse-direction fixed-width selector.
Can I use Power Query to split text by a fixed width from the right?
Yes. If you load your data into Power Query, you can select the column, go to Home > Split Column > By Number of Characters. Enter your character count, and select 'Once, as far right as possible' to achieve this exactly.
How do I deal with spaces at the end of my text messing up the RIGHT formula?
If your cells have trailing invisible spaces, the RIGHT formula will count them as characters. You can fix this by wrapping your cell reference in the TRIM function first, like this: =RIGHT(TRIM(A1), 5).
Is it possible to split text by a specific character delimiter from the right?
If you want to split by a delimiter (like a hyphen or comma) specifically searching from the right, you can use Power Query's 'Split Column by Delimiter' and choose 'Right-most delimiter', or use a complex formula combining RIGHT, LEN, and SEARCH functions.
