Splitting a text based on a pattern in Excel is possible, and it's super easy
- The MID function. The MID function is one of the very first Excel functions, but its capability increases if you use it with an array.
- Write your pattern in an array. If your pattern is always the same, you can specify it, and Excel will split your text according to it.
- Tricks! Write the output in any position 😀The beauty of this trick is that you can write the substring anywhere in your output.
Old Technique (the one everyone used 😉)
Let's say you have a date in the format YYYYMMDD. You can use this technique to convert it to a date or write the following formula.
=RIGHT([@[Date YYYYMMJJ]],2)&"/"&MID([@[Date YYYYMMJJ]],5,2)&"/"&LEFT([@[Date YYYYMMJJ]],4)
Building such a formula is not easy for many people, and it could be a source of confusion. However, there is now a better way to do the same job 😀👍
Split text based on a fixed pattern
Let's start from this situation. You have a list of hexadecimal color codes in column A. You want to split the code into groups of 2 digits: Red, Green, and Blue.
The MID function extracts a substring from a string according to
- The Text
- Start position
- Number of characters to extract
=MID(text,start position, length)
But we can replace the start position and the length with an array. An array is easy to build in Excel. You write the values you want between curly brackets; {value1,value2,value3,....}.
So, in this situation, we want to extract a substring of 2 digits starting from the positions 1, 3, and 5. And like that, with a single formula, we have split the string based on a pattern.
Extract different lengths
In this other example, we have VINs (VIN = Vehicle International Number). A VIN has the following information.
- The World Manufacturer Identifier (WMI) is the first 3 digits
- The Vehicle Descriptor Section (VDS), between digits 4 and 9
- Vehicle identification (VIS), the last 8 digits
In this case, we must write two arrays
- The first array is the starting position of the 3 substrings: 1, 4, 10
- The second array of the length of each substring: 3, 6, 8
Return the result in another order
Last situation, your output can also be written in a different order than the original text. The best example is a date with the format YYYYMMDD
- The length array is the same for both formulas {2,2,4}
- The start position array must reflect our output.
- For the Date format DD/MM/YYYY, the pattern to split the date is {7,5,1}
- For the Date format MM/DD/YYYY, the pattern is {5,7,1}
19/02/2025 @ 06:54
The new REGEX-function is an alternative method
=REGEXREPLACE(A1,
"(\d{4})(\d{2})(\d{2})", "$3$2$1")
19/02/2025 @ 09:17
Yes, true. But my tricks is more accessible for anyone 😉
Thanks for sharing.
04/01/2025 @ 01:47
For "dates" presented like this...
20240713
you could produce the text dates you showed earlier like this...
=TEXT(Table1[Date YYYYMMJJ],"0000\/00\/00")
If you wanted real dates from them, just prefix with 0+ like this...
=0+TEXT(Table1[Date YYYYMMJJ],"0000\/00\/00")
04/01/2025 @ 02:11
True 👍 Or "--" works also
04/01/2025 @ 02:53
I do not like the double negative sign as I find it does not stand out enough... the 0+ is much easier to see, so I tend to use that.
21/11/2024 @ 04:56
I like this trick. Awesome.
21/10/2024 @ 18:19
Good stuff!