If I have something like:

0.04, 0.04, 0.09, 0.38, 0.67, 0.92, 0.94

in one cell and I want to split the 7 values going down 7 cells, so all the values are in cell B9 but I want each value to be in B9:B15?


I usually use another app to separate it.

I'd do:

  1. Open Notepad++. (It doesn't work on Notepad only)

  2. Copy the values from the cell and paste on the Notepad++

  3. Highlight the first ", " (comma and space in between the values) then copy (ctrl + C)

  4. Ctrl + F for find

  5. Click Replace Tab

enter image description here

  1. Click on the radio button Extended at the bottom part.

  2. Choose "\n", which means next line by typing it on the field next to "Replace with".

  3. Click "Replace All" button.
  4. Check the Notepad, it should be as expected.

enter image description here

  1. Copy the values on the excel.

If anyone could make it a lot easier, that'll be great.




=TRANSPOSE(SPLIT("0.04, 0.04, 0.09, 0.38, 0.67, 0.92, 0.94", ","))


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.