Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
I have just converted text to columns, excellent! However two minor complications. 1. there is a blank row btween each text row, which I don't want, and too many to remove individually (over 2000) 2. one part of the data is a grid reference and was formerly (20,192) now because of the delimiter being a comma it has put in two separate columns as: (20 and 192) and I would like to either combine them again as before in one column or remove the parenthisis and have them in two columns. tx in advance -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=515708 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
Here's one way to delete blank rows:
1. Insert a "helper" column A. Number the first 3 rows by manually entering 1, 2, and 3. Highlight those three cells and note the little black box on the lower right corner of the black outline: right-click on that black box, and drag the highlight down for the remaining rows. When you release, select Fill Series. This numbers each row in the range. 2. Sort on any other column. This will cause blank rows to group together. Delete the blank rows. 3. Re-sort on your helper column. This brings the data back to its original layout. Delete the helper column. For the other difficulty, if one of your alternatives is remove the parenthisis and have them in two columns, could you simply do a search and replace? Search for ( and replace it with nothing, then search for ) and replace it with nothing? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
ASAP Utilities, a free add-in available from www.asap-utilities.com has
features to solve both problems for you....... Vaya con Dios, Chuck, CABGx3 "tghcogo" wrote: I have just converted text to columns, excellent! However two minor complications. 1. there is a blank row btween each text row, which I don't want, and too many to remove individually (over 2000) 2. one part of the data is a grid reference and was formerly (20,192) now because of the delimiter being a comma it has put in two separate columns as: (20 and 192) and I would like to either combine them again as before in one column or remove the parenthisis and have them in two columns. tx in advance -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=515708 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
Thanks Dave O both solutions worked a treat, I should have thought of them myself, I was thinking more of formulae. It's so easy sometimes to miss the simple solutions, and over complicate.:) -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=515708 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
Hi, I hope you're still reading! This is exactly what I need to do. How
did you accomplish this? Sean "tghcogo" wrote: I have just converted text to columns, excellent! However two minor complications. 1. there is a blank row btween each text row, which I don't want, and too many to remove individually (over 2000) 2. one part of the data is a grid reference and was formerly (20,192) now because of the delimiter being a comma it has put in two separate columns as: (20 and 192) and I would like to either combine them again as before in one column or remove the parenthisis and have them in two columns. tx in advance -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=515708 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unwrap text to columns? | Excel Discussion (Misc queries) | |||
Text to columns - one way street? | Excel Discussion (Misc queries) | |||
How do I convert from text to columns automatically on import? | Excel Discussion (Misc queries) | |||
comparing columns of text (cross-searching) | Excel Discussion (Misc queries) | |||
Text to columns | Excel Discussion (Misc queries) |