Text to columns

  • Thread starter Thread starter tghcogo
  • Start date Start date
T

tghcogo

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, an
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 separat
columns as:

(20 and 192)

and I would like to either combine them again as before in one colum
or remove the parenthisis and have them in two columns.


tx in advanc
 
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?
 
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.:)
 
Hi, I hope you're still reading! This is exactly what I need to do. How
did you accomplish this?

Sean
 
Back
Top