# imported comma seperated data

#### Kiwi den

##### Board Regular
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

##### Active Member
Hi
Try

Code:
``````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``````

Last edited:

#### Kiwi den

##### Board Regular
I was after a formula rather than a code please

#### alansidman

##### Well-known Member
Are you interested in a Power Query Solution?

#### Kiwi den

##### Board Regular
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

##### Well-known Member
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:

Code:
``````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``````

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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
1Bob, Carl, Kevin, Bill, Olly, Steve
2Bob
3Carl
4Kevin
5Bill
6Olly
7Steve
8
Split values

Last edited:

#### Peter_SSs

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

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

#### Kiwi den

##### Board Regular
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),"#"," "))

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

Threads
1,086,229
Messages
5,388,584
Members
402,126
Latest member
kalcerro_1