Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to rows...at wits end
Hi all,
I've gotten some really great help here before and I'm hoping I'll be able to get some again because I'm at wits end. I'm trying to get a script to do text to rows. The following is how my spread sheet looks...a few hundred rows of cells with data points separated by commas: Accel Partners, Weber Capital Management, LLC, Invesco Private Capital, MeriTech Capital Partners, Oak Investment Partners, Technology Crossover Ventures, Venrock Associates, Doll Capital Management, Telefonos de Mexico SA de CV (NYSE:TMX), Whitman Capital, LLC, AT&T Inc. (NYSE:T), Alcatel (ENXTPA:CGE), Shell Internet Ventures BV, Pilgrim Baxter Hybrid Partners, Lake Street Capital LLC I'm looking for each one to be on separated onto it's own row, i.e. accel partners weber capital management invesco private capital etc. Can someone please help? i've been staring at this for over an hour now and getting nowhere fast! Thanks so much in advance!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to rows...at wits end
I think the easiest way to do it is to highlight all your name data rows and
copy to word. Make sure you save the word file as a .txt file. Then close. Go back to Excel and open that same .txt file, from wherever you saved it, into excel. It will open the import wizard and walk you through the import of delimited data. Just make sure you select the comma separator from step two. This will pull all of your names into their own cells. Then it will be a lot easier to tag columns and move them over and down to col A. I know it's not perfect but it works. " wrote: Hi all, I've gotten some really great help here before and I'm hoping I'll be able to get some again because I'm at wits end. I'm trying to get a script to do text to rows. The following is how my spread sheet looks...a few hundred rows of cells with data points separated by commas: Accel Partners, Weber Capital Management, LLC, Invesco Private Capital, MeriTech Capital Partners, Oak Investment Partners, Technology Crossover Ventures, Venrock Associates, Doll Capital Management, Telefonos de Mexico SA de CV (NYSE:TMX), Whitman Capital, LLC, AT&T Inc. (NYSE:T), Alcatel (ENXTPA:CGE), Shell Internet Ventures BV, Pilgrim Baxter Hybrid Partners, Lake Street Capital LLC I'm looking for each one to be on separated onto it's own row, i.e. accel partners weber capital management invesco private capital etc. Can someone please help? i've been staring at this for over an hour now and getting nowhere fast! Thanks so much in advance!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to rows...at wits end
One thing i just noticed in your example is that some entries have a €œ, LLC€
and some don't. For the ones that do it will separate them so you may want to keep that in mind and correct in word with CTRL H, find and replace. Simply Find ", LLC" and Replace with "_LLC" where _ is really a space. Do this before you save the word file as a .TXT file. Find and replace is supported in .TXT files. Then press on with the save and then import into excel. " wrote: Hi all, I've gotten some really great help here before and I'm hoping I'll be able to get some again because I'm at wits end. I'm trying to get a script to do text to rows. The following is how my spread sheet looks...a few hundred rows of cells with data points separated by commas: Accel Partners, Weber Capital Management, LLC, Invesco Private Capital, MeriTech Capital Partners, Oak Investment Partners, Technology Crossover Ventures, Venrock Associates, Doll Capital Management, Telefonos de Mexico SA de CV (NYSE:TMX), Whitman Capital, LLC, AT&T Inc. (NYSE:T), Alcatel (ENXTPA:CGE), Shell Internet Ventures BV, Pilgrim Baxter Hybrid Partners, Lake Street Capital LLC I'm looking for each one to be on separated onto it's own row, i.e. accel partners weber capital management invesco private capital etc. Can someone please help? i've been staring at this for over an hour now and getting nowhere fast! Thanks so much in advance!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to rows...at wits end
Look after your LLCs please
Sub Macro1() Dim lrow As Integer Dim i As Integer lrow = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(1, 1), Cells(lrow, 1)).TextToColumns Destination:=Range("A1"), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _ TrailingMinusNumbers:=True Columns(1).Insert For i = 1 To lrow If Not IsEmpty(Cells(i, 2)) Then Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Copy Cells(Rows.Count, 1).End(xlUp).PasteSpecial Transpose:=True End If Next i End Sub wrote in message oups.com... Hi all, I've gotten some really great help here before and I'm hoping I'll be able to get some again because I'm at wits end. I'm trying to get a script to do text to rows. The following is how my spread sheet looks...a few hundred rows of cells with data points separated by commas: Accel Partners, Weber Capital Management, LLC, Invesco Private Capital, MeriTech Capital Partners, Oak Investment Partners, Technology Crossover Ventures, Venrock Associates, Doll Capital Management, Telefonos de Mexico SA de CV (NYSE:TMX), Whitman Capital, LLC, AT&T Inc. (NYSE:T), Alcatel (ENXTPA:CGE), Shell Internet Ventures BV, Pilgrim Baxter Hybrid Partners, Lake Street Capital LLC I'm looking for each one to be on separated onto it's own row, i.e. accel partners weber capital management invesco private capital etc. Can someone please help? i've been staring at this for over an hour now and getting nowhere fast! Thanks so much in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
Can anyone tell me how to hide rows that have 0 values and text. | Charts and Charting in Excel | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Selective deletion of rows containing certain text. | Excel Worksheet Functions |