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
Video
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
- SUBSTITUTE
- REPT
- COLUMNS
- TRIM
Solution
- 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
