Parsing data in Excel with functions

I have a dataset that looks like col A and I want to split it up eventually with B,C, & D. They are separated by _

1     A                           B         C         D
2     IJ~ABC_TS~DEF_TP~GHI        IJ~ABC    TS~DEF    TP~GHI

I was able to get Col2 by doing this:

=LEFT(A2, SEARCH("_",A2,1)-1)

For the next part, I think the best way to do it is do a combination of the RIGHT and LEFT function.

How do I use the RIGHT function in a way where I find everything to the right of the value of Col2 in Col1?

My final output should look like this:


Once I do this, I want to simply take the same LEFT function on this new value to get the result of col C.

I cannot figure out how to do the RIGHT function though.

This is my attempt:


I get an error though.

Any help would be great, thanks!

2 answers

  • answered 2018-01-11 20:59 Jeeped

    Try this in B1,

    =TRIM(MID(SUBSTITUTE($A1, "_", REPT(" ", LEN($A1))), (COLUMN(A:A)-1)*LEN($A1)+1, LEN($A1)))

    Fill right for the second and third.

  • answered 2018-01-12 19:57 kmk_qba

    Cell B1 = FIND("_",A1,1)
    Cell C1 = =FIND("_",A1,B1)+B1
    Cell D1 = =MID($A1,1,B1-1)
    Cell E1 = =MID($A1,$B1+1,$C1-$B1-1)
    Cell F1 = =MID($A1,$C1+1,10)

    You can then copy the formula down and then Copy from D1 to F100 'Where ever you data stops and then do a PasteSpecial Values to D1 and then you can Delete Columns B and C