PDA

View Full Version : cleaning a cell



Mykasd
08-29-2007, 03:12 PM
I am copying and pasting from Adobe but the problem is that a bunch of items (with corresponding number IDs) are pasting in one cell. I want to be able to use macros to split that cell apart into two columns (one with number ID and other with name), and how ever many rows.

Any suggestions?

mdmackillop
08-29-2007, 03:14 PM
Can you post the cell contents so we can see the type of data?

austenr
08-29-2007, 03:16 PM
Have you tried text to columns?

Mykasd
08-29-2007, 03:20 PM
Actually i want to split this into 3 columns. when you paste into excel (i've tried to paste a different way, paste special, and that doesnt work either), it all comes out in one cell/multiple cells like so:

23 Apple 340002 5 Banana 2345 34 Carrot 2346676 35 Celery 1005
10 Peanuts 103425 50 Pear 12565.....etc
.
.
.
etc

Mykasd
08-29-2007, 03:20 PM
and what i want is this:
ID Name

Mykasd
08-29-2007, 03:22 PM
whoops, what i want is this:

ID NAME AMOUNT
23 Apple 340002
5 Banana 2345
etc

mdmackillop
08-29-2007, 03:29 PM
Sub DoSplit()
Dim Arr
Dim i As Long, j As Long, k As Long
Arr = Split(Range("A1"))
For i = 0 To UBound(Arr) / 3
For j = 1 To 3
If k > UBound(Arr) Then Exit Sub
Cells(i + 5, j) = Arr(k)
k = k + 1
Next
Next
End Sub