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

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?

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

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

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