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:
TS~DEF_TP~GHI
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:
=RIGHT(A2, SEARCH(B2,A2,1))
I get an error though.
Any help would be great, thanks!
2 answers

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.

Cell B1 = FIND("_",A1,1) Cell C1 = =FIND("_",A1,B1)+B1 Then Cell D1 = =MID($A1,1,B11) Cell E1 = =MID($A1,$B1+1,$C1$B11) 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