removing tabs from data

[Peter Herman]

Hi there,
I have a column where the data is displaying on separate lines within each
cell. I would like the data to display on one line. As an example:

"Shed a little light in your office or study with this amazing dual table
lamp from Gregor, featuring florescent bulbs and an adaptive socket.
Stylish design, great in any room
Twin pole lamp
Innovative on/off pull chain design
Polished steel finish
White fabric shade"

All of this data is in one cell and I need it to be on one line within the
cell. The tabs are messing up a report that is running with tabs as the
delimeter as the report is adding each line as a separate delimeter column.
As I have 10,000 cells of data I do not want to have to go into each cell
manually. Is there a function or format I can use to remove the tabs and have
the data display as one line?
Thanks so much in advance,
Zev

 

[Peter Herman]

I guess I need the opposite of a line break

 

[Peo Sjoblom]

select all cells in question, do format>cells>alignment, remove wrap text,
then do edit replace and in the find box hold down alt and type
010 on the numpad, put a space in the replace box and click OK

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)

 

[PeterAtherton]

Peter
One way is to use a macro. Press Alt + F11 to open the VB Editor, choose
Insert, Module and copy the folling code.

Switch back to the sheet select all the lines to be altered (you might want
to try a few lines first or create a copy file and work with this)

Open the MAcro Window ALT + F8 or Tools, Macro and run the code
Sub clearReturns()
Dim str, c
For Each c In Selection
str = Replace(c, Chr(10), " ")
c.Value = str
Next
End Sub

Regards
Peter

 

[Peter Herman]

Thanks Peo,
This does not seem to be working....I removed the wrap text and then held
down the ALT key when I typed 010 in the find box. (nothing appears) then I
put space in the replace box. Sometimes I got an error message saying the
formula was too long and other times nothing happened. Any advice?
Thanks,
Zev

 

[Peter Herman]

Thanks Peter,
Unfortunately, I lost you from folling code. What is the folling code? I was
also not clear about where the formula begins and ends at the bottom. Any
advice?
Thanks,
P

 

[Gord Dibben]

When you disable wrap text do you see little square hollow boxes where the
breaks would be?

These are the carriage returns.

Type the 010 using the Numeric keys from the Number pad as Peo stated.

You won't see anything in the box while you hit ALT + 010

If still no joy, try 013

To really see what's in there, download Chip Pearson's CELLVIEW add-in.

http://www.cpearson.com/excel/CellView.htm


Gord Dibben MS Excel MVP

 

[PeterAtherton]

Sorry Peter

I meant the following code. Copy into the VB Editor (ALT + F11), Insert a
Module and return to the worksheet. Select a few lines of data, Press Alt +
F8 select the code and click Run

Peter