imported comma seperated data

[Kiwi den]

I have a sheet that i want to import data to (sheet1 B2) this data is seperated by a comma i want to seperate and create it listed in a single column on (sheet 2 B2, B3, B4 etc)
can someone please help with a formula
thanks in advance

 

[Peter_SSs]

https://www.dropbox.com/preview/dummy file for test.xlsx?role=personal

That link doesn't take me to your file, it takes me to my own DropBox where it says (naturally) it cannot find your test.xlsx file.

You have to click Share, then Create Link, then Copy link

 

[Kiwi den]

https://1drv.ms/x/s!AvQI0x4kvuaciWfHRLfmR0b44sBh?e=zyVGkk

 

[Peter_SSs]

Thanks for the link.
When I open that file, remove the x from the front of that formula and press Enter the formula correctly returns Bob. Copied down it returns the other names, though "John Boy" becomes "JohnBoy".
That is why I provided a different version in post 8. The post 8 formula also works for me in the file you provided.

Upshot is that if you have tried the other things I suggested in the thread and the formula still will not be accepted into the cell then I am at a loss as to the reason.

 

[Kiwi den]

Darn... Thanks anyway for taking the time and the effort you put in is much appreciated

 

[Peter_SSs]

Sorry I can't be more help. You could try building the formula up from inside to out to try to locate the problem. That is, see if you can put these formulas in the sheet.
=A$1
then
=SUBSTITUTE(A$1," ","")
then
=SUBSTITUTE(SUBSTITUTE(A$1," ",""),",",REPT(" ",100))
etc

 

[alansidman]

Have you tried my Power Query Solution? In 2016 it is called Get and Transform and is located on the Data Tab of the Ribbon.