How to Split Repeated Characters in Excel using REGEX and TEXTSPLIT
Question details
The user wants to separate consecutive repeated characters (letters or numbers) from a single text string into distinct individual cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing text manipulation or data cleaning where consecutive duplicate characters in a string need to be isolated into distinct adjacent columns.
- Observed behavior
- The repeated characters remain clustered in a single text string, requiring a dynamic formula to insert custom delimiters and split the text precisely where the characters repeat.
Ensure you are using a version of Microsoft 365 that supports the latest array functions and the newly introduced REGEX functions, as these features may not be available in older standalone Excel versions.
Split Repeated Characters Using REGEXREPLACE and TEXTSPLIT
Use a combination of regular expressions and the TEXTSPLIT function to quickly identify repeating characters, insert a delimiter, and split the data into adjacent cells.
This method requires Microsoft 365. The REGEXREPLACE function identifies duplicate consecutive characters and inserts a pipe (|) delimiter between them. TEXTSPLIT then separates the modified string into multiple cells based on that delimiter.
Click on the empty cell where you want the split data to begin spilling horizontally.
Type the formula: =TEXTSPLIT(REGEXREPLACE(A2,"(.)(?=\\1)","\\1|"),"|") (assuming your source text is in cell A2).
Press Enter. The formula will automatically spill the separated text chunks into the adjacent columns.
Split Characters Using a Reusable LAMBDA Function
If your Excel version does not support REGEX functions but supports LAMBDA, you can create a custom recursive function to achieve the same text-splitting result.
Experience Powerful Spreadsheet Capabilities with WPS Office
If you are looking for a highly compatible and cost-effective office suite, WPS Office provides robust formula support and excellent data processing tools. It is lightweight, free to use, and designed to seamlessly handle complex spreadsheets.
- 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook to continue working seamlessly.
- 3. Apply Formulas and Edit: Utilize WPS Office's extensive built-in functions and array formulas to manage and clean your data.
Frequently Asked Questions
What does the REGEX pattern "(.)(?=\1)" mean in Excel?
This regular expression captures any single character using `(.)` and uses a positive lookahead `(?=\1)` to check if the very next character is identical to the one just captured. This allows the formula to target the exact point where consecutive characters repeat.
Why does my Excel show a #NAME? error when using REGEXREPLACE?
The #NAME? error usually occurs because your specific version of Excel does not support REGEX functions yet. These functions are rolling out progressively to Microsoft 365 users and are not available in older standalone versions like Excel 2019 or 2021.
Can I split text by repeated characters using Power Query instead of formulas?
Yes, but it is complex. Power Query typically splits columns using static delimiters. Splitting dynamically based on repeating consecutive characters requires writing advanced custom M code to evaluate the character sequences iteratively.
