Address Block Problem

If there is a hard CR (ie there's three lines of data in each cell) they're separated with the ASCII code 0010. You can use this in the find/replace dialog box:

FInd ALT & 0010 (numberpad)
Replace " "

Originally Posted by longfisher

I use web robots to harvest contact information from public websites and from the HTML code on those sites. Those robots often return "address blocks" like the one below:

"7 Gentlewind Pl
Spring, TX 77381-6183
(936) 273-6922"

As you can see the address blocks contain, generally speaking, three rows of data. And, the rows are separated by a carriage return line feed.

Now, I don't need to break this address block into its respective rows, although I'd like to know how Excel might do this for future reference. But what I do need to do is to display this data in a single cell linearly and in the cell height of only one row.

Any idea how that might be done?

I've tried Edit/Replace but I can't seem to get it to work as I can't copy paste the character for the line feed into the find field.

LongFisher

If three rows of data are in single cell. You can uncheck the wrap text option to bring it to one line.
Right click > Format Cells... > under Alignment tab uncheck the Wrap text option.

Originally Posted by sweep

This will only work until the cell is edited again. The presence of the ASCII 0010 character causes reversion.

If there would be a need to edit cells they can be by following way.

press Ctrl+H
in Find what: single space
in Replace with: single space
and press Replace All