Hi Nadine, Did you format the cell with Wrap text?
Dave. Date: Fri, 13 Aug 2010 08:46:15 -0700 From: [email protected] Subject: Re: $$Excel-Macros$$ Need to remove Alt + Enter To: [email protected] Thanks Dave. This still shows the info as all one line/sentence. I still need the info to show as 3 lines/sentences in one cell. :( Nadine From: Dave Bonallack <[email protected]> To: "[email protected]" <[email protected]> Sent: Fri, August 13, 2010 6:30:32 AM Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter Hi Nadine, If your original text is in A3, try this formula in another cell: =SUBSTITUTE(TRIM(SUBSTITUTE(A3,CHAR(10)," "))," ",CHAR(10)) You will need to format the formula cell with 'Wrap Text' Alt+Enter is a CHAR(10) character. The formula substitutes all these for spaces, then applies the TRIM function which gets rid of all leading and trailing spaces, then substitutes the remaining spaces back to CHAR(10). Regards - Dave. Date: Thu, 12 Aug 2010 07:20:09 -0700 From: [email protected] Subject: Re: $$Excel-Macros$$ Need to remove Alt + Enter To: [email protected] Dave, Is there a way to do this but have it only happen if there's nothing after the Alt Enter? For example, if there were 4 rows but the last one didn't have anything in it, only the last one would be gone? Example below: Entered Test Test2 Test3 Formula Result Test Test2 Test3 Thabks. Nadine From: Dave Bonallack <[email protected]> To: "[email protected]" <[email protected]> Sent: Mon, August 9, 2010 6:33:27 AM Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter You're welcome. Dave. Date: Mon, 9 Aug 2010 05:31:11 -0700 From: [email protected] Subject: Re: $$Excel-Macros$$ Need to remove Alt + Enter To: [email protected] Thanks so much. I used the clean function and it worked perfectly. This was a great help! From: Dave Bonallack <[email protected]> To: "[email protected]" <[email protected]> Sent: Sat, August 7, 2010 10:14:21 PM Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter Hi Nadine, The Alt+Enter leaves a character which has an ascii value of 10. So there are a couple of things you could try. I think the CLEAN function gets rid of this character, so if your data is in Col A, in Col B put CLEAN(A2) and copy down. Or you could try SUBSTITUTE(A7,CHAR(10),"") which should do the same thing. If you need to leave an ordinary space in place of the Alt+Enter character, then try SUBSTITUTE(A7,CHAR(10)," "). With VBA we could actually over-write the data with the corrected data, if that would be better. Sub RemoveChar10() Worksheets("Sheet1").Columns("A").Replace What:=Chr(10), Replacement:="" End Sub Again, if you need a regular space instead of the carriage return, put a space between the "" so it becomes " " Note that the character you have may be 13, not 10, so you'll have to experiment. (The Help calls Char(10) 'Line Feed', and Char(13) 'Carriage Return' but they both seem to do the same thing). Regards - Dave. Date: Sat, 7 Aug 2010 10:12:07 -0700 From: [email protected] Subject: $$Excel-Macros$$ Need to remove Alt + Enter To: [email protected] I often receive a file that has extra Alt+Enter in it which makes the cells long and therefore the files long. Is there anyway I can remove these? The # rows is 700+ - it can vary. Thanks. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
