By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

How do I separate data in one column into two columns and then make the columns turn

P: 4
I have sample output data that comes out in column form like this:

1:1
2:3
4:5

I need to separate the values into individual columns and then make it go across rows to end up looking like this:

1 1 2 3 4 5

I want to do this in Excel or Access. I can make excel separate it into 1 1 OR move it to rows but I can't figure out how to do both.
Apr 24 '14 #1
Share this Question
Share on Google+
7 Replies


P: 28
you can use Split & array()

Expand|Select|Wrap|Line Numbers
  1. Dim i as Integer
  2. Dim xStr as String
  3. Dim xArr() As String
  4.  
  5. xStr = "1:1"
  6. xArr = Split(xStr, ":")
  7.  
  8. For i= LBound(xArr) To UBound(xArr)
  9.   Debug.Print xArr(i)
  10. Next
Apr 24 '14 #2

P: 4
@anvidc
So where do I type this?
Apr 24 '14 #3

P: 28
You Data is in excel or access or maybe you can upload some sample
Apr 24 '14 #4

Rabbit
Expert Mod 10K+
P: 12,430
Why do you need to do this? It sounds like you're normalizing and then denormalizing your data. You should avoid denormalization unless you have a very good reason to do so because it just causes more headaches later on.
Apr 24 '14 #5

P: 4
@Rabbit
I have an output file from a machine that needs to be reformatted to run in analysis software that runs in Command line and it's input requires it in row format, separated and without the colons.
Apr 24 '14 #6

P: 4
@anvidc
This is a small sample of my data output
Attached Files
File Type: xlsx example.xlsx (9.6 KB, 315 views)
Apr 24 '14 #7

P: 28
This may not the best solution, but should help

1.Create a Module then paste this code in VB (Alt+F11)
2.Create a Button then assign to SlpOut Macro

Expand|Select|Wrap|Line Numbers
  1. Sub SlpOut()
  2.  
  3. Dim xC As Range
  4. Dim xArr() As String
  5. Dim i As Long
  6. Dim LRow As Long
  7.  
  8. For Each xC In Range("B1:B" & Range("B1").End(xlDown).Row)
  9.     xArr = Split(xC.Cells.Value, ":")
  10.     For i = LBound(xArr) To UBound(xArr)
  11.         LRow = WorksheetFunction.CountA(Range("D:D")) + 1
  12.  
  13.         Range("D" & LRow).Value = xArr(i)
  14.     Next i
  15. Next xC
  16.  
  17. End Sub
  18.  
Apr 25 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.