How can I achieve this in excel:

enter image description here

I have a very long list that I want to do the same on that list. Is there any quick way?


In column A is your list then in B1 put the formula and drag it down.


In C1 put the below and drag it down


Your list will get populated as desired.

enter image description here

  • can you make the formula movable to other columns ? (a relative formula I mean) – acman123 Jan 22 '17 at 21:41
  • Are you referring to your data being in Column I and split in L & M? The same formula shall work in L & M as well, you will have to just change the column reference to I:I instead of A:A – patkim Jan 22 '17 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.