How to take the values from one cell into multiple cells going down vertically?

Question

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?

Answer 1

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

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

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

8. Click "Replace All" button.
9. Check the Notepad, it should be as expected.

10. Copy the values on the excel.

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

Answer 2

=TRANSPOSE(SPLIT(A9, ","))

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