Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to column/row
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to column/row
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 --------------- Jacob Skaria "Daniel M" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to column/row
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Daniel M" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to column/row
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 "Daniel M" wrote: 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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Daniel M" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to subtract one text column based on their text column MS xls. | Excel Discussion (Misc queries) | |||
Find a text from a column in a text string within another column? | New Users to Excel | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
I want to put the text in the cell of column B in front of the the text in column D. | Excel Programming | |||
Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) |