Parsing data in Excel with functions
I have a dataset that looks like col
A and I want to split it up eventually with
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:
For the next part, I think the best way to do it is do a combination of the
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
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!
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,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