Announcement

Collapse
No announcement yet.

Find/Replace Carriage Returns?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find/Replace Carriage Returns?

    Is there anyway to use Find/Replace to delete all the carriage returns in a column of cells? Thanks in advance!

  • #2
    Hi Bernard2o6

    Welcome to ozgrid.com

    Here is the best way I know of.

    Select any cell that has the character you want removed.
    Form the Formula Bar highlight the character only then copy (Ctrl+C).
    Now go to Edit>Replace.
    In the Find what: box paste (Ctrl+V) the clipboad content.
    Leave the Replace with: box empty and click Replace All.

    To find out the character code you can paste it into an empty cell then reference it with the CODE function. For example

    =CODE(A1)

    Comment


    • #3
      Hi Bernard2o6,

      Here is a VBA alternative. Select the column that needs the replacement applied to.
      Now go to the VBE (ALT+F11) immediate window (CTRL+G) and enter the following,

      Code:
      Selection.Replace chr(13)," "
      which will replace carriage_returns with a space.

      Cheers
      Andy

      ps: Dave, I could not get the copy/paste to work with CR's
      Last edited by Dave Hawley; November 3rd, 2008, 10:45.

      Cheers
      Andy

      Comment


      • #4
        Hmmm, you are right Andy. Could have sworn I have done this without VBA before.

        Comment


        • #5
          Thank you for responding so quickly Dave and Andy!

          Andy-
          I'm quite new to utilizing VBA for Excel. Could you tell me the next few steps after entering Selection.Replace chr(13)," " in the immediate window? I'm not quite sure what I'm to do next.

          Dave-
          Thanks for trying. I too was hoping that this could be done without VBA since I don't have any experience with VBA.

          Regards,

          Bernard

          Comment


          • #6
            Bernard -

            What you should probably do is highlight all the cells from which you want to delete the carriage returns first, then go into VBA and find the window for the sheet you are working on (or any Module for that matter).

            Type in the following:

            Code:
            Sub delchrs()
            Selection.Replace chr(13), " "
            End Sub
            Then, with the cursor anywhere within that code go to "Run" > "Run Sub/UserForm"
            This should do the trick. Post back if it doesn't work.

            -jmhans
            Last edited by Dave Hawley; November 3rd, 2008, 10:46.

            Comment


            • #7
              Hi jmhans-

              I believe the macro ran, but I believe there are still carriage returns. Please forgive my lack of understanding, as far as I know, I'm doing everything correctly. Will there be some confirmation that the macro ran?

              Please advise. Thanks again!

              Regards

              Comment


              • #8
                Perhaps you could post an example workbook with a few records that fail the methods suggested.

                Assuming the code was run then maybe the carriage return is actually something else.

                Cheers
                Andy

                Cheers
                Andy

                Comment


                • #9
                  Andy-

                  Here's an example of what I cut and paste into a cell:

                  Example List
                  <P>
                  <B>Applications:</B>
                  <UL>
                  <LI>All cars
                  </UL>
                  <P>
                  <B>Installation Tip: Check local laws.</B>
                  <P><I>Sold indivuduall</I><P>

                  There are a lot cells like this in the column. I'm just trying to delete all the carriage returns with a short cut. They are formatted with HTML tags so that we can upload it into our database.

                  Thank you again for your help!

                  Comment


                  • #10
                    I would hazard a guess that the carriage return is in fact a linefeed.

                    For this you can use the Find/Replace dialog.
                    Select the column and then press CTRL+H to display the dialog.
                    In the Find What: text box enter the following value ALT+0010 making sure to use the number keypad. You will not see anything appear in the textbox.
                    You can leave the Replace With: blank.

                    You may also have to remove the wrap setting for the cells to get it to appear as a single height cell.

                    Cheers
                    Andy

                    Cheers
                    Andy

                    Comment


                    • #11
                      That did the trick Andy!

                      A "complex" problem for the common man, is like breathing for the highly skilled.

                      Thanks everyone for their input. I will be using these skills here on out!

                      Kind Regards,

                      Bernard

                      Comment


                      • #12
                        Re: Find/Replace Carriage Returns?

                        I had these exact issues in a export from an MDB file I am working on. I had both the chr(13) and ALT+0010 characters present in one of the fields. This thread helped me immensely. Thank you.

                        Comment

                        Working...
                        X