Splitting a text based on a pattern in Excel

Splitting a text based on a pattern in Excel
Last Updated on 30/04/2024
Reading time: 3 minutes

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)

Old technique to split text on a pattern

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.

How to split the hexa color codes
  • 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.

Splitting with a pattern of 2 digits in Excel

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
Splitting a VIN with a pattern

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

How to convert the date YYYYMMDD with the MID function
  • 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}
Splitting date to return dd mm yyyy
  • For the Date format MM/DD/YYYY, the pattern is {5,7,1}
Splitting text date to return mm dd yyyy

Leave a Reply

Your email address will not be published. Required fields are marked *

Splitting a text based on a pattern in Excel

Reading time: 3 minutes
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)

Old technique to split text on a pattern

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.

How to split the hexa color codes
  • 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.

Splitting with a pattern of 2 digits in Excel

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
Splitting a VIN with a pattern

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

How to convert the date YYYYMMDD with the MID function
  • 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}
Splitting date to return dd mm yyyy
  • For the Date format MM/DD/YYYY, the pattern is {5,7,1}
Splitting text date to return mm dd yyyy

Leave a Reply

Your email address will not be published. Required fields are marked *