Splitting data in a cell

[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

 

[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"

 

[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

 

[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

 

[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
+-------------------------------------------------------------------

 

[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!

 

[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
+-------------------------------------------------------------------

 

[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

 

[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 |
+-------------------------------------------------------------------+

 

[Toppers]

typo ... should be johntopley