Split Text using Multiple Functions

Welcome to Excel Jet Consult blog. In this tutorial, we will see how to split text using multiple functions. In part one, we saw how to split text using the rarely known FILTERXML Function. We will use the same data-set and achieve the same outcome in this blog post. Let’s get started


Kindly watch the video for in-depth explanation

Sample Data

In the Sample data below, we have all the data in column A separated by hyphen delimiter (-). Our goal is to write a formula that will split these in each columns

List of Functions

To achieve the text split, we are going to use the following text functions:

  • LEN
  • MID
  • REPT
  • TRIM


  • In cell B1, execute the formula: =TRIM(MID(SUBSTITUTE($A1,”-“,REPT(” “,LEN($A1))),LEN($A1)*(COLUMNS($A:A)-1)+1,LEN($A1)))

Kindly refer to the video for in-depth explanation of the above formula

  • Click Enter and copy down the formula

In the caption below, we can see that we have successfully split the data into separate columns

Posted in Blog and tagged .

Leave a Reply

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