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

 

[mohadin]

Hi
Try

Sub test()
Dim Data
    With ThisWorkbook.Sheets("Sheet1")
        Data = .Range("b2")
    End With
    Data = Split(Data, ",")
    Sheets("sheet2").Cells(2, 2).Resize(UBound(Data)) = Application.Transpose(Data)
End Sub

 

[Kiwi den]

I was after a formula rather than a code please

 

[alansidman]

Are you interested in a Power Query Solution?

 

[Kiwi den]

splitiing imported data into seperate cells

I have a sheet with imported data in cell A1 ( Bob, Carl, Kevin, Bill, Olly, Steve)
I would like to seperate these into seperate cells all in column A
So A2 = Bob
A3 = Carl
A4 = Kevin
A5 = Bill
A6 = Olly
A7 = Steve

Is there a formula at I can use to do this, or come someone direct me to where I can get this info

 

[kweaver]

Re: splitiing imported data into seperate cells

Do you want something to do all the steps or are you willing simply to use Text To Columns, then Copy, Paste Special, Transpose?

The macro recorder would give you this:

Sub Separate()
'
' Separate Macro
'

'
    Range("A1").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
    Rows("1:1").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

 

[Peter_SSs]

Re: splitiing imported data into seperate cells

I have merged your two threads since they are asking the same question. Please refer to #12 of the Forum Rules and also #6 of the Forum Use Guidelines


For your question, try this copied down.

Excel Workbook
	A
1	Bob, Carl, Kevin, Bill, Olly, Steve
2	Bob
3	Carl
4	Kevin
5	Bill
6	Olly
7	Steve
8
Split values

 

[Peter_SSs]

Actually, in case any names might contain spaces, perhaps safer with something like this.

Excel Workbook
	A
1	Bob, Carl, Kevin, John Boy, Olly, Steve
2	Bob
3	Carl
4	Kevin
5	John Boy
6	Olly
7	Steve
8
Split values

 

[Kiwi den]

Thank you, however I cannot get the formula to work, i have copied it into cell A2 and it just shows the formula, not theresult. Any thoughts?
The text you have in Red it shows an an error =TRIM(SUBSTITUTE(MID(SUBSTITUTE(A$1,",",REPT("#",100)),ROWS(A$2:A2)*100-99,100),"#"," "))

 

[Peter_SSs]

i have copied it into cell A2 and it just shows the formula, not theresult. Any thoughts?

Most likely A2 is formatted as Text.
Select A2 and format it to General then press F2 followed by Enter to re-confirm the formula & then copy down.