2

I have thousands of data on a column that is similar to

1651651465,"15654"
41654544,"4814"

they are all in a single column (like A1, A2, A3,....)

I want to save the number before , to A1(A2,A3,A4...) and the number inside "" to B1 (B2,3,...). how can I do it?

then it will be like:

       A            B
1   1651651465    15654
2   41654544       4814
3   etc
4   etc

If you know how to do it in open office, I can use open office then save it as MS excel.

1
  • can you specify which version of Office are you using ?
    – Shekhar
    Oct 15, 2013 at 17:17

2 Answers 2

3

Select the column.
Go to Data tab and select Text to Columns.
Select delimited on the first page , click Next.
Select comma in the next page , click Finish.

1

Try this small macro:

Sub reformit()
    Dim s As String, N As Long, L As Long
    s = Chr(34)
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For L = 1 To N
        v = Replace(Cells(L, "A"), s, "")
        ary = Split(v, ",")
        Cells(L, "A") = ary(0)
        Cells(L, "B") = ary(1)
    Next L
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .