Lesson#111: Extract parts of text separated by a 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 has four parts separated by “/”. Each part has a different meaning. The first means the short form of the station name, the second part is level, the third part is span and the fourth type. These parts may have different character lengths. 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 that we can face anywhere working with spreadsheets. You may have different examples with the same type of 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 occurrences of separating special characters into another special character. Those new special characters should be different and never used before in the text. For example, I converted the 2nd occurrence of “/” into “*” and the 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 the 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 formulas would give the same results.

What is Text to Columns? Read here the page.