Simple Macro to Single Column Values


I have the attached spreadsheet with two columns of data - the first with a single value (col A) in and the second (col B) with either no value, a single value or multiple values with many values in separated by a comma (all multiple values are separated by a comma).

I could like to keep Col A as it is however I would like all the values in column B to be separated (if there are multiple in a cell) and copied to column A as single values or just copied to column A where there is a single value (i.e. no comma indicating multiple values) and just ignored if no value.

Could someone tell me if this is possible and how I may tackle this?

Many thanks in advance.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Quick question your delimiter in Column-B are not constant at row number-2 its semicolon but then in row number -9 you have semi-colon and colon..

Do you have a constant delimiter like you said in your question comma or it will be always like this??

gisvpnAuthor Commented:
Hi there it could be either a semi colon or a colon! Sorry should have said this too!
gisvpnAuthor Commented:
A comma should never be used!
Saurabh Singh TeotiaCommented:
I believe this is what you are looking for...

Sub movedata()
    Dim rng As Range, cell As Range
    Dim st As Variant, str As String
    Dim lr As Long, lr1 As Long, i As Long
    lr = Cells(Cells.Rows.Count, "B").End(xlUp).Row

    Set rng = Range("B1:B" & lr)
    For Each cell In rng
        lr1 = Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1
        If Trim(cell.Value) <> "" Then
            If Len(cell.Value) > 10 Then
                str = Replace(cell.Value, ":", ";")
                st = Split(str, ";")
                For i = 0 To UBound(st)
                    lr1 = Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1
                    Cells(lr1, "a").Value = st(i)
                Next i
                Cells(lr1, "a").Value = cell.Value

            End If

        End If

    Next cell

End Sub

Open in new window

Your workbook..i ran the code on sheet2..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gisvpnAuthor Commented:
Great quality answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.