Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO | *automatically* parse comma separated text to multiple ce
hi all,
i have searched the posts here, but have not really found the answer to my particular problem. i have an evaluation sheet i am building. in one column i am capturing key words for a particular topc. these key words are comma separated. example: 1 TOPIC: KEYWORDS: --------------------------------------------------------- 2 RFID Shopping, security, inventory 3 UPC Shopping, inventory what i want is twofold: i want to be able to parse out each word per cell per row into a separate area (range of cells or other worksheet) with duplicates removed. my result would be: 1 KEY WORDS: --------------------------------------------------------- 2 inventory 3 security 4 shopping doesn't have to be alphabetized, but could be nice. if this cannot be done on-the-fly but can be done with a macro (e.g. a button i have to explicitly press to generate this), then great. i have check out a ton of sites and see bits and pieces, but i am too green with VBA to know how to make it work. any help would be greatly appreciated! thanks, doon Using: - Excel 2003 (v.11 build 6113) - WinXP SP2 - 512 MB RAM ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO | *automatically* parse comma separated text to multiple ce
sorry, my title was too long and doesn't say anything useful in the postings
list view. it should read: *automatically* parse comma separated text to multiple cells although on retrospect, i would leave off the "automatic" part. doon |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO | *automatically* parse comma separated text to multiple ce
You can select column B and do
data|text to columns delimited by comma to separate the data into adjacent cells. To get the list of unique key words, you could do that same thing, then build a giant column (copy|pasting at the bottom of the giant column), then do data|advanced filter|unique records only) to get the unique list. In code, it could look like: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim myRng As Range Dim iCol As Long Dim DestCell As Range Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add CurWks.Range("b:b").Copy _ Destination:=NewWks.Range("A1") With NewWks .Range("a:a").TextToColumns Destination:=.Range("A1"), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) For iCol = 2 To .Range("a1").CurrentRegion.Columns.Count Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) .Range(.Cells(1, iCol), .Cells(.Rows.Count, iCol).End(xlUp)).Copy _ Destination:=DestCell Next iCol .Range("b1", .Cells(1, .Columns.Count)).EntireColumn.Delete .Rows(1).Insert .Range("a1").Value = "header" .Range("a:a").AdvancedFilter action:=xlFilterCopy, _ copytorange:=.Range("b1"), unique:=True .Columns(1).Delete .Rows(1).Delete 'remove leading spaces! .Columns(1).TextToColumns Destination:=.Range("A1"), _ DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) .Columns(1).Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm doon wrote: hi all, i have searched the posts here, but have not really found the answer to my particular problem. i have an evaluation sheet i am building. in one column i am capturing key words for a particular topc. these key words are comma separated. example: 1 TOPIC: KEYWORDS: --------------------------------------------------------- 2 RFID Shopping, security, inventory 3 UPC Shopping, inventory what i want is twofold: i want to be able to parse out each word per cell per row into a separate area (range of cells or other worksheet) with duplicates removed. my result would be: 1 KEY WORDS: --------------------------------------------------------- 2 inventory 3 security 4 shopping doesn't have to be alphabetized, but could be nice. if this cannot be done on-the-fly but can be done with a macro (e.g. a button i have to explicitly press to generate this), then great. i have check out a ton of sites and see bits and pieces, but i am too green with VBA to know how to make it work. any help would be greatly appreciated! thanks, doon Using: - Excel 2003 (v.11 build 6113) - WinXP SP2 - 512 MB RAM ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO | *automatically* parse comma separated text to multipl
hi dave,
thanks for your reply. i will give it a shot. i am trying a bit of code someone else sent me, but i think i will end up taking bits from both. this is really educational and helpful. cheers! d |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO | *automatically* parse comma separated text to multipl
But it's still not a good idea to multipost to several newsgroups.
If you really think that it's important to send the message to lots of newsgroups, then send one message to all the groups (not an individual message to each). doon wrote: hi dave, thanks for your reply. i will give it a shot. i am trying a bit of code someone else sent me, but i think i will end up taking bits from both. this is really educational and helpful. cheers! d -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum multiple cells with different numbers and text. | Excel Discussion (Misc queries) | |||
COUNTIF formula multiple text exclusions | Excel Discussion (Misc queries) | |||
Multiple text sheets into a master worksheet as you enter data | Excel Worksheet Functions | |||
multiple text files URGENT | Excel Discussion (Misc queries) | |||
importing multiple text files??? | Excel Discussion (Misc queries) |