Splitting data in a cell

J

JamesBurrows

Hi all,

I have data that looks something like this

6"/7"/3" DATA1
1" DATA2
2"/1" DATA3
8" DATA4
9" DATA5
8" DATA6
1"/2" DATA7

what i need to be able to do is to split the data so that when there i
more than one size in one cell the whole row is copied as many times a
there are sizes so the result would look like. The copying of the dat
is not a problem but what code do I use to split it all up.

6" DATA1
7" DATA1
3" DATA1
1" DATA2
2" DATA3
1" DATA3
8" DATA4
9" DATA5
8" DATA6
1" DATA7
2" DATA7

Hope this makes sense.

Thanks in advanc
 
J

JamesBurrows

I have just learned i need to do something to do with delimiters an
have the following code

Instr (1,Cells(rowNum, colNum).Value,"/"

but what i dont know now is how to take the information from before an
after the / and what if it has 3/s (ie 8"/7"/2"
 
T

Toppers

James,

Try this: change w/sheet names as required.

Sub splitData()
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
rr = 2
For r = 2 To lastrow '<==== Assumes data starts on row 2
i = 1
Do
j = InStr(i, .Cells(r, "A"), "/")
If j <> 0 Then
n = j - i
Worksheets("Sheet2").Cells(rr, "A") = Mid(.Cells(r, "A"), i, n)
Worksheets("Sheet2").Cells(rr, "B") = .Cells(r, "B")
i = j + 1
Else
Worksheets("Sheet2").Cells(rr, "A") = Mid(.Cells(r, "A"), i, 255)
Worksheets("Sheet2").Cells(rr, "B") = .Cells(r, "B")
End If
rr = rr + 1
Loop Until j = 0
Next r
End With
End Sub

HTH
 
J

JamesBurrows

Thanks very much for that it does exavtly what i needed it too, all iv
got to do is work out how to change the variables and worksheets t
what i need but otherwise, spot on, thanks
 
J

JamesBurrows

I have tested the code and am begginning to understand it, but am reall
new to programming and am not sure how to intergrate it into my code a
a function that is called in my code or taking individual lines an
placing them intop my code, sorry Im sure im being really dopey but i
I can get this sorted then thats my job on this finished, would be ver
grateful of any help, see file attached. Thanks AGAIN

+-------------------------------------------------------------------
|Filename: CSV Test File.zip
|Download: http://www.excelforum.com/attachment.php?postid=4877
+-------------------------------------------------------------------
 
T

Toppers

Which worksheet do you want the results in? and which column(s) consistitutes
"Data1" etc. If you can post an example it would help.

May be some delay before next reply!
 
J

JamesBurrows

Ok im getting there now but am still struggling, I have attached my cod
and hope this helps show where im trying to go

+-------------------------------------------------------------------
|Filename: Code.txt
|Download: http://www.excelforum.com/attachment.php?postid=4880
+-------------------------------------------------------------------
 
J

JamesBurrows

In answer to your earlier question:

The data should be read from Line List Sheet and copied to Tags CSV.

The sizes will be read from Line List Sheet (Colum A) and copied t
Tags CSV (Colum B). And everytime a Size is copied across the whole ro
should populate with information, wether that be a size, as in one siz
or 1of three sizes. I am sorry if my explaination is a bit poor bu
hopefully you get my drift
 
T

Toppers

James,
Attached is my attempt to insert code.
I limited the testing to the first nine rows of data AND I commented
out a statement in FILLFORM which said "Update" error as this was
overriding the pipe size.

I note you had other blocks of data with headings further down the list
- why are the headings there rather than a single set at the top?

You can contact me direct (to avoid long discussions on the NGs) at
toppers<at>nospam.john.topley.fsnet.co.uk

Remove the nospam from the above.

John


+-------------------------------------------------------------------+
|Filename: CSV Test File X.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4881 |
+-------------------------------------------------------------------+
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top