Lesson#111: Extract parts of text separated by special character

Here I will show you how to extract parts of text separated by a special character. In the above image I am having some structure id. Each text have four parts separated by “/”. Each parts has a different meaning. The first means short form of station name, second part is level, third part is span and the fourth is type. These parts may have different character length. I have to separate each part of the text. You can do it very easily by Text to Columns but when you need a dynamic formula then use this trick. This is a generic problem which we can face anywhere working with spreadsheets. You may have different examples with same type problem.

1st I will insert an extra column for easiness of understanding the next formulas. In the new column I will convert the 2nd and 3rd occurrence of separating special characters into other special character. Those new each special characters should be different and never used before in text. For example, I converted the 2nd occurrence of “/” into “*” and 3rd occurrence of ‘/” into “^”.

I have made the formula =SUBSTITUTE(SUBSTITUTE(A2,”/”,”*”,2),”/”,”^”,2)

This formula converted KDGD/CCL/P1-P2/T1-1 into KDGD/CCL*P1-P2^T1-1

For the first part I have used the formula =LEFT(B2,FIND(“/”,B2)-1)

FIND function finds out the position of “/” and LEFT function takes out the part before “/”.

For the 2nd part I used the formula =MID(B2,FIND(“/”,B2)+1,FIND(“*”,B2)-FIND(“/”,B2)-1)

This formula takes out the part of text between “/” and “*”.

For the 3rd part =MID(B2,FIND(“*”,B2)+1,FIND(“^”,B2)-FIND(“*”,B2)-1)

This formula takes out the part between “*” and “^”.

For the 4th and last part I used the formula =MID(B2,FIND(“^”,B2)+1,LEN(B2)-FIND(“^”,B2))

It can also be done with the formula =RIGHT(B2,LEN(B2)-FIND(“^”,B2))

In this case both formula would give same results.

What is Text to Columns? Read here the page.

Leave a Reply

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

Copyright 2017-2018@Excelabcd