# Splitting a text based on a pattern in Excel

Last Updated on 30/04/2024

Splitting a text based on a pattern in Excel is possible, and it's super easy

1. 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.

2. 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.

3. 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
• Text
• The start position
• The length 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

So, in this case, we must write 2 arrays, one for the first position and the second for the length to extract.

• The second argument is the array of the starting position of the 3 substrings: 1, 4, 10
• The third argument is the 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}

# Splitting a text based on a pattern in Excel

Last Updated on 30/04/2024

Splitting a text based on a pattern in Excel is possible, and it's super easy

1. 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.

2. 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.

3. 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
• Text
• The start position
• The length 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

So, in this case, we must write 2 arrays, one for the first position and the second for the length to extract.

• The second argument is the array of the starting position of the 3 substrings: 1, 4, 10
• The third argument is the 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}