Remove Carriage Returns

August 28, 2010 | Comments Off

QUESTION: How do I remove unwanted carriage returns (displayed as tiny boxes) also known as “hard returns” from my 2003/XP/2000/97 Excel spreadsheet?

ANSWER: Simple. Use a substitute function e.g. =SUBSTITUTE(A2,CHAR(13),”")

Below we have a screen capture of an Excel spreadsheet. Column A row 2 contains the original text that includes the unwanted carriage returns highlighted in yellow.

HOW TO REMOVE UNWANTED CARRIAGE RETURNS:
1. First, BEFORE you do anything, make a BACKUP COPY of your original spreadsheet
2. Modify substitute function to reflect column and row your original text is currently in e.g. A2
3. Then copy substitute function e.g. =SUBSTITUTE(A2,CHAR(13),”")
4. Select empty cell e.g. B2
5. Paste substitute function into empty cell e.g. B2
6. Apply (copy) to all other column B cells by pulling the cell corner control vertically down the page

This will remove all carriage returns (which is a 13 in Ascii, a format effector which moves the active position to the first character position on the same line).

You could also remove other unwanted Ascii characters and/or tags e.g. <br> all at the same time. The substitute function would then look like this: =SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),”"),”<br>”,”")

adding ,”<br>”,”") to the end of your substitute function.

To view other control characters (Ascii values), review the Ascii Chart. Now you know how to remove those unwanted carriage returns. Give it try!

Another related topic: Video Demo: Merge cells or combine their contents or click Excel Merge How To for written article. For more information on this topic or related topics please review Microsoft Office Support.

Tech Tips

Use inline AutoComplete

March 19, 2009 | Leave a Comment

Q&A: “Is there any way to fix my login so I don’t have to type in my entire email address and password every time?” If you are using Internet Explorer 5 or above then yes. There is an IE browser setting called “Use inline AutoComplete”.

HOW TO: (PART I)
1. Located in your browser menu bar select “Tools”
2. Then select “Internet Options”
3. Next select the “Advanced” tab
4. Under the heading “Browsing” place a check in the box next to “Use inline AutoComplete”
5. Lastly, click “Apply” then click “OK”

*Takes effect after you restart Internet Explorer

HOW TO: (PART II)
1. Located in your browser menu bar select “Tools”
2. Then select “Internet Options”
3. Next select the “Content” tab
4. Under the heading “AutoComplete” click the “Settings” button
5. Place a check in the box next to “Web Addresses“, “Forms“, and “User names and passwords on forms”
6. Lastly, click “OK”