imported comma seperated data

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
107
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
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,103
Office Version
2019
Platform
Windows
Are you interested in a Power Query Solution?
 

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
107
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
Joined
May 8, 2018
Messages
875
Office Version
365, 2010
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
Joined
May 28, 2005
Messages
42,765
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
42,765
Office Version
365
Platform
Windows
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
Joined
Feb 17, 2014
Messages
107
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
Joined
May 28, 2005
Messages
42,765
Office Version
365
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

This Week's Hot Topics

Top