In MS Excel, I have cells that contain data separated with a semicolon. For example one cell could have the following data: Apple;Banana;Watermelon (see Starting state figure)

I also have columns labeled as "Apple", "Banana", etc. I need to somehow split this data so that the splitted part of the data goes to corresponding column (see Desired state figure).

Starting state

Desired state

I have tried using the data tool called "Data to columns" but I only managed to split the data of the cell but not place the result data parts to correct positions.

1 Answer 1


In B2 enter:


and copy both across and downward:

enter image description here

(The dollar signs in the formula are important)

  • This was exactly what I was looking for, thank you! Nov 12, 2020 at 17:31
  • @user13797882 You are quite welcome. Nov 12, 2020 at 17:40

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.