• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

Simple Macro to Single Column Values

Hello,

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.

Regards

GISVPN
Book1---Copy.xlsx
0
gisvpn
Asked:
gisvpn
  • 3
  • 2
1 Solution
 
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??

Saurabh...
0
 
gisvpnAuthor Commented:
Hi there it could be either a semi colon or a colon! Sorry should have said this too!
0
 
gisvpnAuthor Commented:
A comma should never be used!
0
 
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
                cell.Clear
            Else
                Cells(lr1, "a").Value = cell.Value
                cell.Clear

            End If

        End If

    Next cell

End Sub

Open in new window


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

Saurabh...
Book1---Copy-1.xlsm
0
 
gisvpnAuthor Commented:
Great quality answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now