How to Split Slash-Delimited Text into Separate Excel Cells
Question details
The user wants to split a text string separated by slashes (e.g., "00001/ / /29100/") and extract only the first and fourth fields while preserving leading zeros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific data fields from a delimited text string into separate columns.
- Observed behavior
- Needs to isolate specific fields from a slash-separated string, ignore empty spaces, and treat the output as text to keep formatting like leading zeros intact.
Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions, such as Microsoft 365, as the primary solution relies on the newer TEXTSPLIT and CHOOSECOLS functions.
Use TEXTSPLIT and CHOOSECOLS Functions
The most efficient way to split text by a delimiter and instantly extract specific columns while treating the output as text.
By nesting the TEXTSPLIT function inside CHOOSECOLS, you can break apart the string by the slash character and simultaneously grab only the index numbers of the fields you need.
Click on the empty cell where you want the first extracted value to appear.
Type the formula =CHOOSECOLS(TEXTSPLIT(A1,"/"),1,4) assuming your source text is in cell A1. Adjust the column index numbers (1 and 4) if your target fields change.
Press Enter. The formula will automatically spill the first and fourth fields into adjacent cells.
Ignore Empty Fields and Spaces with TEXTSPLIT
Use advanced array arguments in TEXTSPLIT to handle strings with inconsistent spacing or consecutive slashes.
Clean Delimiters Using SUBSTITUTE and TRIM
An alternative approach that cleans the delimiters first before splitting, which is helpful for highly irregular text strings.
Split and Process Text Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like TEXTSPLIT and CHOOSECOLS, making it incredibly easy to split delimited text, extract specific columns, and manipulate data arrays without complex VBA code.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your delimited text.
- 2. Apply TEXTSPLIT: Type the =TEXTSPLIT() formula into your target cell to divide the slash-delimited data.
- 3. Extract specific fields: Wrap it in the =CHOOSECOLS() function to target exact field placements, like the 1st and 4th column.
- 4. Generate the results: Press Enter to see the extracted text spill automatically into the neighboring cells while keeping formatting intact.
Frequently Asked Questions
Why does Excel drop the leading zeros when I split text?
Leading zeros are dropped if Excel automatically converts the split result into a standard number format. Using functions like TEXTSPLIT keeps the results as text strings by default, which naturally preserves the leading zeros.
What should I do if my text has an unpredictable number of slashes?
You can use the ignore_empty argument in the TEXTSPLIT function by setting the fourth parameter to TRUE (e.g., =TEXTSPLIT(A1,"/",,TRUE)). This prevents empty fields from appearing in your resulting array if there are consecutive slashes.
Is TEXTSPLIT available in older versions of Excel?
No, TEXTSPLIT and CHOOSECOLS are dynamic array functions available only in Microsoft 365 and newer versions of Excel. For older versions, you must use a combination of LEFT, RIGHT, MID, and FIND functions, or rely on the Data > Text to Columns wizard.
