text to column/row

[Daniel M]

I have some data i need to separate out. the data looks like this:

partnumber description location
1234 resistor R1, R3, R10

I need to be able to break the location out by comma. but i need to place it
in the next row under the first location. i think need to fill the rows under
it with the other data. ie: partnumber and description. it should look like
this:

1234 resistor R1
1234 resistor R3
1234 resistor R10

Can someone give me a hand with this? I know i can do text to column but it
may be 1 column or 100. Even if i can get this i still need to place the
value under the previous value. There will also be several lines so i need to
"INSERT" a row not just put it in the next row down! thanks.

 

[Jacob Skaria]

With your data in Col A starting from row1; try the below macro which will
convert the data and write to ColB. Try and feedback

Sub Macro()

Dim lngARow As Long, lngBRow As Long
Dim strData As String
Dim arrData As Variant, intTemp As Integer

lngARow = 1
lngBRow = 1
Do While Range("A" & lngARow) <> ""
arrData = Split(Range("A" & lngARow), ",")
Range("B" & lngBRow) = arrData(0)
lngBRow = lngBRow + 1
strData = Trim(Left(arrData(0), InStrRev(arrData(0), " ")))
For intTemp = 1 To UBound(arrData)
Range("B" & lngBRow) = strData & " " & Trim(arrData(intTemp))
lngBRow = lngBRow + 1
Next
lngARow = lngARow + 1
Loop

End Sub

If this post helps click Yes

 

[Daniel M]

This is a start but still not exactly what i need. First i changed the ranges
to support my data. Col C and D

22006 Cap, 10uf MLC Y5V 16V 1206 C1, C2, C18, C52
20022 Res, 330K 5% 0402 R9, R11
22000 Cap, 0.1uf MLC X7R 1% 25V 0402 C3, C4, C5, C6, C7, C8, C9, C10, C19,
C20, C21, C22, C23, C25, C26, C38

Here is my data.

The output looks like this...

22006 Cap, 10uf MLC Y5V 16V 1206 C1, C2, C18, C52 C1
20022 Res, 330K 5% 0402 R9, R11 C2
22000 Cap, 0.1uf MLC X7R 1% 25V 0402 C3, C4, C5, C6, C7, C8, C9, C10, C19,
C20, C21, C22, C23, C25, C26, C38 C18
C52
R9
R11
C3
C4
C5
C6
C7
C8
C9
C10
C19
C20
C21
C22
C23
C25
C26
C38
I need it to insert rows when it copies the data so that the partnumber and
description stay with the string being parsed. ie:

22006 Cap, 10uf MLC Y5V 16V 1206 C1, C2, C18, C52 C1

C2

C18

C52
20022 Res, 330K 5% 0402 R9, R11 R9
R11

This way i can fill the pn and description down for each value parsed.

 

[Daniel M]

Ok so i got it to insert a row after it pastes the data but then it stops
because it tries to go to the next line (2) and it should go to the next line
i havent inserted. Any ideas? Looks like i loose the variable value when i
exit the loop.

Dim lngARow As Long, lngBRow As Long
Dim strData As String
Dim arrData As Variant, intTemp As Integer

lngARow = 1
lngBRow = 1
Do While Range("A" & lngARow) <> ""
arrData = Split(Range("C" & lngARow), ",")
Range("D" & lngBRow) = arrData(0)
lngBRow = lngBRow + 1
strData = Trim(Left(arrData(0), InStrRev(arrData(0), " ")))
For intTemp = 1 To UBound(arrData)
Rows(intTemp + 1).Select
Selection.Insert Shift:=xlDown
Range("A" & intTemp).Select
Selection.Copy
Range("a" & intTemp + 1).PasteSpecial
Range("D" & lngBRow) = strData & " " & Trim(arrData(intTemp))
lngBRow = lngBRow + 1
Next
lngARow = lngARow + 1
Loop