Parsing data in Excel with functions

Question

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!

I want to have a an excel script where if someone pastes a new dataset in the columns, it will automatically parse the data rather than clicking buttons for Text to Columns. The goal is to make it less manual which a function would accomplish — Nick Knauer
– Nick Knauer, Commented Jan 12, 2018 at 15:27

user4039065 · Accepted Answer · 2018-01-11 20:59:39Z

3

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 Jan 11, 2018 at 20:59

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Knauer Over a year ago

Thanks @Jeeped. This works for this scenario. In my actual dataset, there are two columns before A. When I write this function, and replace A with C it doesn't start parsing it from the beginning but rather 2 delims to the right. Any idea why this may be?

Nick Knauer Over a year ago

Nevermind I figured it out. If your column is in C, you need to change everything except for COLUMN(A:A). Thanks!

Nick Knauer Over a year ago

looking further into the formula, this is really intuitive and outside the box! thanks for the help @jeeped

kmk_qba · Accepted Answer · 2018-01-12 19:57:59Z

0

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

answered Jan 12, 2018 at 19:57

kmk_qba

12 bronze badges

Collectives™ on Stack Overflow

Parsing data in Excel with functions

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related