I don't want to scare you but here is the formula, if you insist on doing it in a single formula. This is a mega-formula that I built up from several smaller formulas. It will work for any lengths. It is very difficult to read and maintain.

=LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))),FIND("_",RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))))-1)

If you put your value in A1, you can build up to it using the following formulas in the cells indicated:

A2 =RIGHT(A1,LEN(A1)-FIND("_",A1))

A3 =RIGHT(A2,LEN(A2)-FIND("_",A2))

A4 =RIGHT(A3,LEN(A3)-FIND("_",A3))

A5 =LEFT(A4,FIND("_",A4)-1)

Note that each formula needs to do a find plus know the length of the result of the previous step. That's why the mega-formula is so big, because each formula replaces the previous formula twice, and you repeat the replacement process 3 times.

There might be a slightly simpler way to do this, if it comes to me I'll post again.

## Like this thread? Share it with others