aka_krakur
Active Member
- Joined
- Jan 31, 2006
- Messages
- 438
I have a column of data that is not consistent except for codes are separated by a semicolon. Up until now I manually convert text to table by separating by semicolon in Data, Text to columns in excel.
then I have to do a bunch of sorting becuase end result I need all these codes into one column. I have pasted below the examples and then finally the end result that I need.
END RESULT
Can someone help me with this...possibly a macro that loops until all codes in Column A are separated by semicolon then put on their own distinctive row into one column.
Thanks
then I have to do a bunch of sorting becuase end result I need all these codes into one column. I have pasted below the examples and then finally the end result that I need.
Book2 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | REPORTED_DEV_CLARIFICATION | REPORTED_DEV_CLARIFICATION1 | REPORTED_DEV_CLARIFICATION2 | REPORTED_DEV_CLARIFICATION3 | REPORTED_DEV_CLARIFICATION4 | REPORTED_DEV_CLARIFICATION5 | ||
2 | 1393-N/A;1528-FilterBasket; | 1393-N/A | 1528-FilterBasket | |||||
3 | 1586-Core;2578-N/A; | 1586-Core | 2578-N/A | |||||
4 | 1586-Tip; | 1586-Tip | ||||||
5 | 2578-DeviceComponent; | 2578-DeviceComponent | ||||||
6 | 2578-Stent; | 2578-Stent | ||||||
7 | 1528-FilterBasket;1586-Core;1586-FilterBasket;2578-FilterBasket;1670-AgainstResistance; | 1528-FilterBasket | 1586-Core | 1586-FilterBasket | 2578-FilterBasket | 1670-AgainstResistance | ||
8 | 1339-Tip; | 1339-Tip | ||||||
9 | 1339-Shaft;1467-IntroducerSheath; | 1339-Shaft | 1467-IntroducerSheath | |||||
Sheet1 |
END RESULT
Book2 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
13 | REPORTED_DEV_CLARIFICATION | |||||
14 | 1393-N/A | |||||
15 | 1586-Core | |||||
16 | 1586-Tip | |||||
17 | 2578-DeviceComponent | |||||
18 | 2578-Stent | |||||
19 | 1528-FilterBasket | |||||
20 | 1339-Tip | |||||
21 | 1339-Shaft | |||||
22 | 1528-FilterBasket | |||||
23 | 2578-N/A | |||||
24 | 1586-Core | |||||
25 | 1467-IntroducerSheath | |||||
26 | 1586-FilterBasket | |||||
27 | 2578-FilterBasket | |||||
28 | 1670-AgainstResistance | |||||
Sheet1 |
Can someone help me with this...possibly a macro that loops until all codes in Column A are separated by semicolon then put on their own distinctive row into one column.
Thanks