**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}

Crispo Mwangi

21/10/2024 @ 18:19

Good stuff!