Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
I guess I need the opposite of a line break
"Peter Herman" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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 "Peo Sjoblom" wrote: select all cells in question, do formatcellsalignment, 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) "Peter Herman" <Peter wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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 "PeterAtherton" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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 On Mon, 28 Aug 2006 14:13:02 -0700, Peter Herman wrote: 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 "Peo Sjoblom" wrote: select all cells in question, do formatcellsalignment, 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) "Peter Herman" <Peter wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing tabs from data
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 "Peter Herman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Gathering Data from various tabs | Excel Worksheet Functions | |||
Removing a blank cell between data | New Users to Excel | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions |